asked
How to Prevent users Create Other logins
I currently have multiple users on my DB. All these users are currently Open to do all (Sysadmin and public). I need create restrictions for my users. I basically need them to be able to Create proc edit procs, inserts updates, selects, create views. but not make Changes to any users. or Edit any databases.ETC
You should remove those users from sysadmin role first, and you should add them to db_datareader, db_datawriter for read & write roles, if you need to restrict the permission based on objects then use GRANT SELECT, INSERT, UPDATE, DELETE ON TO The above query grants SELECT, INSERT, UPDATE & DELETE Permission on given table to the user. To add a user to role 'db_datareader' use below query EXEC sp_addrolemember 'db_datareader', 'Myuser' For creating Stored porcedures, use GRANT CREATE PROCEDURE TO . There is a role called db_ddladmin which can be assigned to a user for all DDL changes.
I add to Anuj. If you `GRANT CRETE PROCEDURE`, it is not enough. User also need to have ALTER permission on the `schema` in which the objects will be created. In general if you want to grant users to create any objects in the particular database like tables, views, stored procedures etc, you can make it member of the **`db_ddladmin`** fixed database role again as mentioned by Anuj. See [Permissions of Fixed Database Roles][1] on MSDN for details. And for reading any data in the DB simply add users to the **`db_datereader`** fixed database role as Anuj mentioned. To allow writes (INSERTS, UPDATES) add users to the **`db_datawriter`**. Also ensure, that users are not members of **`sysadmin`** or **`securityadmin`** fixed server roles or **`db_securityadmin**` and **`db_owner`** fixed database roles. You have also ensure that there are no such rights covered by the fixed roles assigned directly to the users. See also: [Permissions (Database Engine)][2] [Permissions of Fixed Server Roles (Database Engine)][3] [Permissions Hierarchy (Database Engine)][4] [sys.database_permissions (Transact-SQL)][5] [sys.fn_builtin_permissions (Transact-SQL)][6] [1]:
http://msdn.microsoft.com/en-US/library/ms189612(v=sql.105).aspx [2]:
http://technet.microsoft.com/en-us/library/ms191291(v=sql.105).aspx [3]:
http://technet.microsoft.com/en-us/library/ms175892(v=sql.105).aspx [4]:
http://technet.microsoft.com/en-us/library/ms191465(v=sql.105).aspx [5]:
http://technet.microsoft.com/en-us/library/ms188367(v=sql.105).aspx [6]:
http://technet.microsoft.com/en-us/library/ms186234(v=sql.105).aspx
12 People are following this question.
