user creation

Question

user creation

sedj

SSC Veteran

Points: 216
More actions
June 9, 2010 at 7:43 am

#147487

i'm quite new to SQL 2005. Anyone have tips on creating a user which only has access to a specific database and can only run specific sp's?
Are there any quick guides/howtos?

Viewing 5 posts - 1 through 4 (of 4 total)

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

Lowell SSC Guru Points: 323482 More actions · Answer 1

so much of what you want depends on specifics...the types of logins,which procs, whether they can read form an tables or not, etc, but this might give you some ideas.

the recommended practice is to create a role with the desired permissions, and then assign users to the role...avoid giving permissions to na actual user in the db; use roles instead.

--add two logins , bob and tom

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'WHATEVER', @deflanguage = N'us_english'

--add this user to permit read and write

END

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

BEGIN

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

END

Use MyDatabase

--create the role

CREATE ROLE [MyDatabaseUsers]

--add only some permissions to execute three procs....

GRANT EXECUTE On PROC1 TO [MyDatabaseUsers]

GRANT EXECUTE On PROC2 TO [MyDatabaseUsers]

GRANT EXECUTE On PROC3 TO [MyDatabaseUsers]

--add users to the database for the matching sql logins

CREATE USER [bob] FOR LOGIN [bob]

CREATE USER [jeff] FOR LOGIN [jeff]

--add the two sql logins, and an example of a windows group to the role

EXEC sp_addrolemember N'MyDatabaseUsers', N'bob'

EXEC sp_addrolemember N'MyDatabaseUsers', N'tjeff'

--EXEC sp_addrolemember N'MyDatabaseUsers', N'NT AUTHORITY\Authenticated Users'

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!

sedj SSC Veteran Points: 216 More actions · Answer 2

I've tried this query and created a user, however this user has full rights to run system stored procedures.

How do i DENY this user to run system stored procedures?

Lowell SSC Guru Points: 323482 More actions · Answer 3

SYSTEM stored procedures? like sp_who? i think that is inherited with CONNECT permissions, but i'm not sure what you are refering to.

can you script out the user you created and show us? what database are we talking about?? the example above would do what you originally asked, did you mis-state or are you asking about procs that exist in MASTER now?

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!

sedj SSC Veteran Points: 216 More actions · Answer 4

yes i mean running stored procedures such as sp_who or sp_configure

How do i script out a user?