How do I add a new user, but with limited priviledges

Question

Post reply

How do I add a new user, but with limited priviledges

mw_sql_developer

SSCoach

Points: 19445
More actions
January 24, 2011 at 6:57 am

#233214

I need help on the following:
1. Need to create a user
2. The user should only have read priviledges.
3. Be able to execute a stored procedure
( To make a long story short this is what the stored proc does:
The stored proc will insert a bunch of records to a table and then execute one final select statement. )
I am going to use this user for an application.

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

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

robinrai3 SSCrazy Points: 2812 More actions · Answer 1

attd is script i did for a database called yellowpages to add myself (robin) with readonly privileges

and execute permission on one stored proc from scratch...hope it helps...script is always re-runnable

since it always deletes and adds

================================================

USE Master

GO

declare@DBNamevarchar(30),

@ServerNamevarchar(30),

@Uservarchar(30)

select@DBName= db_name(),

@ServerName= @@SERVERNAME,

@User= 'dbo'

print'*************************************************************************************************'

print' QMIS_CR159_Add_Robin_Login.sql -- Start - ' + CONVERT(CHAR(20),GetDate(),113)

print'*************************************************************************************************'

print''

print'Server: ' + @ServerName

print'Database: ' + @DBName

print'User: ' + @User

print''

print'----------------------------------------------------------'

print'Add Robin Login '

print'----------------------------------------------------------'

print''

--------------------------------------------------

-- Remove Access If It already Exists --

--------------------------------------------------

use master

go

if exists (select 1 from sys.schemas where name = 'Robin')

DROP SCHEMA Robin

GO

IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'Robin')

DROP USER Robin

GO

IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'Robin')

DROP LOGIN Robin

GO

-------------------

-- Create Login --

-------------------

CREATE LOGIN Robin WITH PASSWORD=N'Yellowg3$', DEFAULT_DATABASE=[YellowPages], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

USE YellowPages

go

-------------------------------------

-- Create User robin in YellowPages --

-------------------------------------

CREATE USER Robin FOR LOGIN Robin WITH DEFAULT_SCHEMA=[dbo]

GO

----------------------------------------------------------------------

-- Grant db_datareader to robin in YellowPages --

----------------------------------------------------------------------

exec sp_addrolemember N'db_datareader', N'Robin'

go

------------------------------------------------

-- Grant permssions on a single stored proc

------------------------------------------------

grant execute on dbo.AddAction to robin

print ''

print 'sanity check : '

print ''

exec sp_helplogins N'Robin'

print'*************************************************************************************************'

print' Finish - ' + CONVERT(CHAR(20),GetDate(),113)

print'*************************************************************************************************'

GO

DBA_Dom SSCrazy Points: 2902 More actions · Answer 2

In order for the procedure being executed the caller needs to either have write permissions or a different user with write permissions needs to be used in the 'with execute as <user>' clause of the procedure that has write permissions.