Add a new user account with certain priviledges

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  January 24, 2011 at 2:26 pm

  #144814

  Hi

  RE: SqL server 2005

  What I need:

  A.) Create a new user

  B.) Assign priviledges

  Here is what I know and don't know

  I can certainly create a new user in SqL SEVER 2005.

  My question is how do I limit this users priviledges.

  Without using the GUI stuff is it possible to do it at the "QUERY WINDOW"

  Here is what I am looking forward to

  1.) User should only be able to read from tables

  2.) User should be able to run stored procedures ( via an application )

  If #2 sounds like a challenge just help me with #1

  Thanks

• Lowell

  SSC Guru

  Points: 323518

  More actions

  January 24, 2011 at 2:33 pm

  #1277479

  one of the options in SSMS is that you can script out any command you are doing in the GUI...

  the way to do it is as follows:

  1. create a role

  2. Add the desired rights to the role...not to a user..

  3. create the login for the user.

  4. create the "user" in the database for that login.

  5. assign the user to that role.

  anyway here is an example:

  create database SandBox

  GO

  USE SandBox

  CREATE ROLE [ReadOnlyPlusExecute]

  --give reader rights to this group

  EXEC sp_addrolemember N'db_datareader', N'ReadOnlyPlusExecute'

  --allowed to run all procs and functions

  GRANT EXECUTE TO [ReadOnlyPlusExecute]

  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

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

  CREATE USER [bob] FOR LOGIN [bob]

  --add this user to our role above

  EXEC sp_addrolemember N'ReadOnlyPlusExecute', 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!