Add user to sql server with the required permission

Question

Post reply

Add user to sql server with the required permission

IT researcher

SSCertifiable

Points: 7567
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?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

sharky Default port Points: 1494 More actions · Answer 1

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

Lowell SSC Guru Points: 323504 More actions · Answer 2

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!