Script to creat user and put into roles

Question

Script to creat user and put into roles

QPR

SSC Eights!

Points: 814
More actions
December 8, 2005 at 2:46 pm

#115633

Hi.
On a regular basis I have to create a new SQL user (from an existing NT logon), give them access to a certain DB and plonk them into roles.
Anyone know of a script whereby I can just provide sql login name, nt account, default db (optional), db access and roles within that db.
The roles would be relevant to that DB (application roles that have been created) as opposed to SQL roles.
Ta

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

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

John Rowan SSC Guru Points: 56440 More actions · Answer 1

When you say application roles, I assume that you mean user database roles? If this is the case, use sp_addrolemember Use this sp, or write your own that calls this SP. All you need to provide is the login name and the roll name. DB permissions are defined by the role and do not need passed in.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

QPR SSC Eights! Points: 814 More actions · Answer 2

Hi John,

We have a 3rd party application with a SQL backend.

In the database are some (non out of the box) roles that allow users to do certain tasks within the application.

Currently I go into EM, add the login to the server, give them access to database XYZ and then put them into any number of given roles e.g. cashier, accounts etc.

This would be much simpler if I had an SP that I could pass params too....

exec spSaveMeTime 'Johns','domain/Johns','XYZ','cashier','accounts',null,null,null

Where:

Johns is the sql login to create

domain/Johns is the NT logon to map it to

XYZ is the permitted database access

cashier and accounts are the roles they are added to within XYZ

Nulls are placeholders for any additional roles they may belong to.

I'm picking there must be entries like this in the Master DB but it's not something I've ever looked at achieving before now.

jfmccabe SSCrazy Points: 2547 More actions · Answer 3

You need to talk to your sysadmin about this.

What we do here if to a series of Windows groups mapped to database roles. They've all got fancy names like MIS1, MIS2 etc but they do the job.

Nobody has a personal login to the database.

Using Cashier and Accounts as above, then you get the sysadmin to create matching Windows groups. You add the groups to the server as logins. You then add these server logins to the database you want as users. Then you add the user to the role.

Next, when you want to assign somebody to a role, you ask the sysadmin to add them to the appropriate group.

When somebody changes jobs, you get the sysadmin to change their group membership.

Notice that you have NO personal logins, it's all managed via Windows groups.

QPR SSC Eights! Points: 814 More actions · Answer 4

Thanks, unfortunately that won't work in our scenario.

Our users all have individual logins to one (or more) databases.

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 5

Just to clarify your terminology, you can't create a SQL login and map it to a Windows login. You can create logins on the SQL Server for Windows users and groups, and when you grant access to specific databases on the server you can assign a different name as a kind of alias. Like you, I prefer to drop the domain names from Windows logins when granting them access to databases. If you enjoy chaos you can give both your Windows and SQL logins different names as users in every database, but you're not creating additional logins.

I don't see why you don't create roles for every useful combination of existing roles so you can assign each user to a single role, unless you really need to micromanage every possible combination. But without judging why you do it that way, your procedure would look like this:

CREATE

PROCEDURE dbo.uspSaveYouTime

@NameInDB sysname,

@WinLogin sysname,

@Database sysname,

@Role1 sysname,

@Role2 sysname,

@Role3 sysname,

@Role4 sysname,

@Role5 sysname,

@Role6 sysname

AS

IF (not (is_srvrolemember('securityadmin') = 1)) begin

raiserror(15247,-1,-1)

Return 1

end

if @Database in ('master', 'msdb', 'tempdb', 'distribution') begin

raiserror('This procedure does not operate on system databases.', 16, 1)

return 1

end

-- Add Windows login to server if it doesn't exist

if not exists(select null from master.dbo.syslogins where name = @loginame)

exec sp_grantlogin @loginame = @WinLogin

-- Give login access to the database