Creating Logins and mapping to databases

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Creating Logins and mapping to databases

Author

Topic

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2012-04-24 : 05:04:02

Hi all

I've got some code that creates a new user onn a SQL Server 2008 R2 box.
The code is:-


exec Reports.dbo.create_new_user @username

This is the Alter procedure statement :-

ALTER PROCEDURE [dbo].[Create_New_User]
	@username sysname
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @SQL NVARCHAR(4000);
	
	SET @SQL = 'CREATE LOGIN [' + @username + '] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[British]'
	EXECUTE(@SQL);
	
END;


Unfortunately, it doesn't map the new user to any databases so I can't use any further code to assign rtoles that user in the relevant database.

When I try to execute the following code:-
[code]
exec [Lumia].dbo.drop_user_role @role_name='db_accessadmin', @username=@user_name

I get an error stating that the user or role does not exist in this database.

I've checked the properties of the new user and there are no databases ticked under user mapping.

I can't add any new roles to assing new users to as 2008R2 doesn't support user-defined roles.

Anyone any ideas of a way around this?

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-24 : 06:42:04

The following link might be helpfull...it has TSQL code to add user, role etc.

[url]http://stackoverflow.com/questions/2775234/tsql-to-map-user-to-database[/url]

Hope this helps.

N 28° 33' 11.93148"
E 77° 14' 33.66384"

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2012-04-24 : 07:17:40

I've looked at that and it doesn't quite do what I need.
I need to create a general login and then assign permissions to the databases afterwards.

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-24 : 08:47:01

Doesn't this do what you want??

alter authorization on database::<dbanme> to <loginname>;

N 28° 33' 11.93148"
E 77° 14' 33.66384"

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2012-04-24 : 09:09:19

Unfortunately not.
If I right-click on a user and select Properties, I get a box which allows me to alter the login properties.
If I then select User Mapping, I get a list of the databases that are available on the server.

What I want to do then (in SQL code) in tick the Map box for all the relevant databases and set the properties with a Default Schema of dbo

opc.three
Starting Member

5 Posts

Posted - 2012-04-24 : 17:44:19

Try adding something like this to your procedure after you create the LOGIN:

SET @SQL = 'USE Lumia; CREATE USER ' + QUOTENAME(@username) + ' FROM LOGIN ' + QUOTENAME(@username) + ' WITH DEFAULT_SCHEMA=[dbo];'
EXECUTE(@SQL);

Make everything as simple as possible, but not simpler. -- Albert Einstein

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-25 : 00:23:39

You are doing it using TSQL....right??...then you can either do it like OPC.three posted while creating the login or you can do it as in the link after creating the login using Alter.

N 28° 33' 11.93148"
E 77° 14' 33.66384"

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2012-04-25 : 03:57:12

Got it working nopw, thanks forlks.
I altered opc.three's code so it would accept parameters and it seems to be working now.

Thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources