Add user to sql server with the required permission

• IT researcher

  SSCertifiable

  Points: 7616

  More actions

  April 10, 2013 at 5:24 am

  #272918

  In my sql server express 2008 r2 i have about 15 login account. Now i want some about 5 users have full permission while some other 5 users with limited access to some database, and other users only read permission. In future i may add other users also. So i was thinking of doing it in simple way than giving permission for every user as it requires giving permission to each database every time a new user is created. So can i create a set of rules or group and then add a new user to that group or rules? (So new user who gets added that group gets the permission automatically from that group). Do i have any kind of facility in sql server which will make this happen?

• sharky

  Default port

  Points: 1494

  More actions

  April 10, 2013 at 6:37 am

  #1605190

  You can add roles and add permissions to the roles. Multiple users can then be mapped to the roles.

• Lowell

  SSC Guru

  Points: 323522

  More actions

  April 10, 2013 at 9:16 am

  #1605286

  well adding a user and granting permissions are a three part process.

  first you really needed to already have a ROLE in your specific database that has the permissions you want to give access to;

  users are also a per-database object, that are tied to a login, so to do the same to a second database, you repeat teh same steps(except if the login exists)

  here's an example of creating a role,and giving that role Read access to the tables, and also execute permission to stored procs and functions you created in that specific database.

  USE [SandBox] --my db for users to do stuff.

  USE [WHATEVER]

  CREATE ROLE [AlmostOwners]

  EXEC sp_addrolemember N'db_ddladmin', N'AlmostOwners'

  EXEC sp_addrolemember N'db_datareader', N'AlmostOwners'

  EXEC sp_addrolemember N'db_datawriter', N'AlmostOwners'

  --can the users EXECUTE procedures? uncomment if true

  GRANT EXECUTE TO [AlmostOwners]

  --allow the users to see view proc and function definitions

  Grant View Definition ON SCHEMA::[dbo] To [AlmostOwners]

  and another role:

  --my limited access role

  CREATE ROLE [ReallyReadOnly]

  --give my new role READ permission to ALL tables

  EXEC sp_addrolemember N'db_datareader', N'ReallyReadOnly'

  --explicitly DENY access to writing

  EXEC sp_addrolemember N'DB_DenyDataWriter', N'ReallyReadOnly'

  --give my new role permission to run the procedures you've created

  GRANT EXECUTE TO [ReallyReadOnly]

  now that the role is created, we can add a LOGIN to the master database

  IF NOT EXISTS (SELECT * FROM MASTER.dbo.syslogins WHERE name = N'bob')

  BEGIN

  EXEC MASTER.dbo.sp_addlogin @loginame = N'bob', @passwd = 'NotARealPassword', @defdb = N'SandBox', @deflanguage = N'us_english'

  END

  --and a group from the Active directory:

  CREATE LOGIN [myDomain\Dev] FROM WINDOWS WITH DEFAULT_DATABASE=[WHATEVER]

  now that a LOGIN exists, lets add a USER, tied to that login, to our database

  --make a user in the db for the matching login

  CREATE USER [bob] FOR LOGIN [bob]

  --make a windows group for a whole buncha people:

  CREATE USER [myDomain\Dev] FOR LOGIN [myDomain\Dev]

  finally, add our user bob to the role we created

  --my read only user

  EXEC sp_addrolemember N'ReallyReadOnly', N'bob'

  --my windows group, comrising of lots of users, are ain a different group:

  EXEC sp_addrolemember N'AlmostOwners', N'myDomain\Dev'

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!