Monday, February 6, 2006

SQL Server: How to create a user login and give it access

This is just a script that I put together to create a user and assign it database access and roles. The login actually existed previously with the wrong roles, so the script starts out by deleting the login from both the database and the server. Here are the commands I entered and the server's responses:

-- Start up OSQL from the command line

-- Set the database to the one we want to
-- grant access to
2> go

-- Drop the login's database access
1> sp_revokedbaccess @name_in_db='USER_NAME'
2> go
User has been dropped from current database.

-- Drop the SQL Server login
1> sp_droplogin @loginame='USER_NAME'
2> go
Login dropped.

-- Add the SQL Server login with the
-- correct password and default database
1> sp_addlogin @loginame='USER_NAME', @passwd='PASSWORD', @defdb='DEFAULT_DATABASE'
2> go
New login created.

-- Grant database access to the login
1> sp_grantdbaccess @loginame='USER_NAME'
2> go
Granted database access to 'USER_NAME'.

-- Grant the login the "db_owner" role
-- to this database
1> sp_addrolemember @rolename='db_owner', @membername='USER_NAME'
2> go
'USER_NAME' added to role 'db_owner'.

This was for a login with "Standard" security-- that is, not a domain user name.

No comments:

Post a Comment

I moderate comments blog posts over 14 days old. This keeps a lot of spam away. I generally am all right about moderating. Thanks for understanding.