Add logins to a database using script.

Question

Post reply

Add logins to a database using script.

Crispin Proctor

SSCoach

Points: 18161
More actions
May 15, 2003 at 6:01 am

#106078

Greetings all.
I am creating a script which will create a DB with all the settings required.
The thing I am battling with is adding uses to the DB (The users already exist on the Servers. They are NT Users)
I want one user as a dbo and some of them to have read write acess.
Would Adding a role to the DB and adding the users to that be the easiest way?
I have tried using sp_grantlogin but nothing gets added.
sp_adduser does the same.
Cheers,
Crispin
Cheers,
Crispin
Why don't you try practicing random acts of intelligence and senseless acts of self-control?
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

Viewing 11 posts - 1 through 10 (of 10 total)

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Hi,

adding a role is always a good idea. It saves you a lot of time for maintenance.

Cheers,

Frank

Edited by - a5xo3z1 on 05/15/2003 06:05:28 AM

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Crispin Proctor SSCoach Points: 18161 More actions · Answer 2

Thanks but what I can't figure out is how do I add a user to the role with Read / Write access or are the permissions per role?

This is driving me mad!

Cheers,

Crispin

Why don't you try practicing random acts of intelligence and senseless acts of self-control?

Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

Crispin Proctor SSCoach Points: 18161 More actions · Answer 3

I sort of answered my own question. Permissions per role.

Looking at EM, you specify permissions per table, proc etc.

Any way of applying read / write to all objects?

Cheers,

Crispin

Why don't you try practicing random acts of intelligence and senseless acts of self-control?

Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

Hi,

I think not to all objects at once in EM, unfortunately. Maybe per script?

That's the reason for a role. You define the permissions just once for the role. And by adding a user you do not have to care about the rights for this user. He has at least the permissions defined by the role.

Cheers,

Frank

Edited by - a5xo3z1 on 05/15/2003 06:33:49 AM

Edited by - a5xo3z1 on 05/15/2003 06:40:56 AM

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 5

quote:
I want one user as a dbo and some of them to have read write acess.

For example,

EXEC sp_grantdbaccess 'Corporate\JeffL', 'Jeff'

GO

EXEC sp_addrolemember 'db_owner', 'Jeff'

sp_grantdbaccess 'Corporate\AnnL', 'Ann'

GO

EXEC sp_addrolemember 'db_datareader', 'Ann'

Crispin Proctor SSCoach Points: 18161 More actions · Answer 6

Thanks.

I have pritty musch got there.

You can use GRANT (SP3 Only) to grant the access to tables etc.

Only "funny" thing is though when you add as user they have access to delete update etc.

I'll have to run through that to remove them and set as I wish.

FYI: The grant help is only avaliable on BOL SP3. You can downoad it from MS > SQL

Cheers,

Crispin

Cheers,

Crispin

Why don't you try practicing random acts of intelligence and senseless acts of self-control?

Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 7

Take a look at the following roles:

db_datareader

db_datawriter

These roles give read or write respectively on all tables and views within a given database. These permissions are implicit, meaning when you add a new table or view, these two roles automatically get the appropriate rights.

If you're looking for execute rights on all stored procedures, my article on the db_executor role talks about building a user-defined role because there isn't one within SQL Server.

http://www.sqlservercentral.com/columnists/bkelley/sqlserversecuritythedb_executorrole.asp

Now these are global solutions, meaning they'll apply to every object that the role is designed to touch. If you want to tailor read/writes to specifics tables, etc., then you'll need to create a user-defined role, as you've already hit upon.

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

K. Brian Kelley
@kbriankelley

DRBuckingham SSC Eights! Points: 996 More actions · Answer 8

Also, you can add (custom) database roles to the db_datareader and db_datawriter roles as well. For instance, you could create an 'ProdXAppUser' role within the database, add 'ProdXAppUser' to db_datareader and db_datawriter, then sp_grantlogin, sp_grantdbaccess, and finally sp_addrolemember.

By maintaining all permissions at the role level, you easily add new users. You may choose to grant specific permissions to the database role (like EXEC for certain SPs and UPDATE on certain tables) rather than using the db_datawriter role.

The database role will retain the permissions whether there are any users remaining in the database or not. It sure beats keeping a script around to run every time a new user is created to give them the permissions individually.

Edited by - drbuckingham on 05/15/2003 6:08:51 PM

David R Buckingham, MCDBA,MCSA,MCP

Crispin Proctor SSCoach Points: 18161 More actions · Answer 9

Thanks Brian,

The article answered some of my questions.

Thought: Could you not delve into SQL and add a trigger to sysObjects, SysColumns(?) so that when a table was created it re-ran the script?

A problem I face with your solution (Mine included) is some of the DB's get restored often. This erases the permissions set by the role. (It actually removes the role)

Solution: For a couple of DB's I have added the users to db_datareader and db_datawriter and granted Create, later update to the DB. This to gets over written by the restore but....

I have also used some of DRBuckingham ideas. I have a script which runs adding xyz to the role and DB.

I have been given the task of "securing" our SQL machines. Currently, all the apps that access SQL use sa. (These are live to the world!)

I am now on a steep learning curve and don't want to screw it up. 🙂

Thanks for the advice!

Cheers,

Crispin

Why don't you try practicing random acts of intelligence and senseless acts of self-control?

Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 10

Triggers on system tables are unsupported, so that's really not an option. Scheduling a job that checks for the existence of the role and adds it if it's missing is always an option.

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

K. Brian Kelley
@kbriankelley