Script to grant user permission

Question

Script to grant user permission

cpj18

SSC Veteran

Points: 232
More actions
March 26, 2011 at 8:14 pm

#99649

Hi,
I need to write a script that will grant a user account permission to login to SQL SERVER without using a stored procedure.
Can anyone help me with this. I have not been having any luck at all.
Thanks.

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

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

Lowell SSC Guru Points: 323546 More actions · Answer 1

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.

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

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]

finally, add our user bob to the role we created

EXEC sp_addrolemember N'ReallyReadOnly', N'bob'

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!

Jim Murphy SSCertifiable Points: 6263 More actions · Answer 2

Nice job Lowell. Very clear and organized explanation and code.

Jim

Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph