Clicky

We have been deleting users from a series of databases using a script EE helped us develop,

Now we need to create a series a users to the Databases.

The user info for the DB would be:

   - username = JohnSmith
   - Database role memebership = DBname
   - everything else is standard.

Can this also be done via script?

asked 12/17/2011 06:33

Ramante's gravatar image

Ramante ♦♦


7 Answers:
hope below articles are helpful:
SQL Server Script to create a new user
http://stackoverflow.com/questions/1601186/sql-server-script-to-create-a-new-user

CREATE USER (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms173463.aspx
link

answered

Inderjeetjaggi's gravatar image

Inderjeetjaggi

with dbsets
as (
select 'db1' dbname
union all
select 'db2'
union all
select 'db3')
select 'use dbname; '+CHAR(10)
+'create user JohnSmith for login JohnSmith;'+CHAR(10)
+'exec sp_addrolemember ' +''''+'DBname'+''''+','+''''+'JohnSmith'+''''
from dbsets
link

answered 2011-12-18 at 02:47:00

mark_gao's gravatar image

mark_gao

So, you have the logins already present right. If yes, and if only you want to create users, you can again do it via t-sql script which goes as:-

Use <dbname>
go

Create User <UserName> For Login <LoginName>
Go

Exec sp_addrolemember <Rolename>, <UserName>
go

If you want to provide more roles to the user, you can proceed with adding rolemember to the user. Here is an example:-
CREATE USER [abc] FOR LOGIN [abc]
GO
EXEC sp_addrolemember N'db_datareader', N'abc'
GO
EXEC sp_addrolemember N'db_datawriter', N'abc'
GO
link

answered 2011-12-18 at 02:52:24

TempDBA's gravatar image

TempDBA

Inderjeetjaggi:
    Checked out the links and gives the error:

    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near 'NewAdminName'.
    Msg 319, Level 15, State 1, Line 1
    Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an    
    xmlnamespaces clause, the previous statement must be terminated with a semicolon.

mark_gao:

    This is just for displaying?

TempDBA:

    Create User is giving error:

    Msg 15007, Level 16, State 1, Line 1
     'abc' is not a valid login or you do not have permission.
link

answered 2011-12-18 at 02:54:46

Ramante's gravatar image

Ramante

It means the login is not present. You have to create login. From 2005 , sql server has started different form of authentication with logins, schemas, users, etc.
So, first create login.
1. If it is windows aunthetication then

USE [master]
GO
CREATE LOGIN [domainName\UserName] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO

2. If SQL Login then
USE [master]
GO
CREATE LOGIN [<loginName>] WITH PASSWORD=N'<password>', DEFAULT_DATABASE=[master]
GO
link

answered 2011-12-18 at 03:04:36

TempDBA's gravatar image

TempDBA

Thanx it worked!
link

answered 2011-12-18 at 03:11:53

Ramante's gravatar image

Ramante

Thanx
link

answered 2011-12-18 at 03:14:26

Ramante's gravatar image

Ramante

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

×4

Asked: 12/17/2011 06:33

Seen: 205838 times

Last updated: 12/17/2011 07:22