January 7, 2009 at 2:20 pm
I have a script where it creates db objects. Before, I start creating my db objects I perform the following steps:
1. From "Master", create new database.
2. Move to New database & create new Schema with dbo authorization.
3. Create new User with my new schema as my default schema.
4. Grant delete, execute, insert, references, select on the new schema to my new user.
5. Using sp_addrolemember map my new user to db_owner.
6. execute as my new user.
7. create my db objects.
8. revert
9. exit
When I perform this operation I'm getting errors that my new "user" is not a valid login or you do not have permission and also "cannot find user because it does not exists or you do not have permission.
I want to know which part i'm missing?
Thanks
January 7, 2009 at 2:30 pm
while you were in master, you should have added a login to the server, and then in your database, add a user to that login
here's a simple example, where I'm creating two roles...one that would be for a couple of developers so they can add tables, and another where some group on the machine would "use" the database:
[font="Courier New"]
CREATE DATABASE Whatever
GO
USE Whatever
--create the Role for my Dev guys
CREATE ROLE [WhateverDEVAdmins]
ALTER AUTHORIZATION ON SCHEMA::[db_ddladmin] TO [WhateverDEVAdmins]
ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [WhateverDEVAdmins]
ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [WhateverDEVAdmins]
--create role for my normal users
CREATE ROLE [WhateverDEVUsers]
ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [WhateverDEVUsers]
ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [WhateverDEVUsers]
--now add specific users to nearly-Admins
IF NOT EXISTS (SELECT * FROM MASTER.dbo.syslogins WHERE name = N'bob')
BEGIN
EXEC MASTER.dbo.sp_addlogin @loginame = N'bob', @passwd = 'NotARealPassword', @defdb = N'WHATEVER', @deflanguage = N'us_english'
--add this user to permit read and write
END
IF NOT EXISTS (SELECT * FROM MASTER.dbo.syslogins WHERE name = N'jeff')
BEGIN
EXEC MASTER.dbo.sp_addlogin @loginame = N'jeff', @passwd = 'NotARealPassword', @defdb = N'WHATEVER', @deflanguage = N'us_english'
END
USE [WHATEVER]
--make a user in the db for the matching login
CREATE USER [bob] FOR LOGIN [bob]
CREATE USER [jeff] FOR LOGIN [jeff]
--add these logins to the proper role
EXEC sp_addrolemember N'WhateverDEVAdmins', N'bob'
EXEC sp_addrolemember N'WhateverDEVAdmins', N'jeff'
CREATE LOGIN [NT AUTHORITY\Authenticated Users] FROM WINDOWS WITH DEFAULT_DATABASE=[WHATEVER]
CREATE USER [NT AUTHORITY\Authenticated Users] FOR LOGIN [NT AUTHORITY\Authenticated Users]
EXEC sp_addrolemember N'WhateverDEVUsers', N'NT AUTHORITY\Authenticated Users'[/font]
Lowell
January 7, 2009 at 2:35 pm
The first thing that comes to mind is that the user isn't being created for an existing login.
Greg
January 7, 2009 at 3:18 pm
I can able to understand the steps. But, wat if I don't want the login password to be embedded into the script. Is there a way the login could be created by the DBA & later this script would use this login to the user & execute the db objects?
Thanks.
January 7, 2009 at 3:47 pm
as long as the logon exists, you could then add lots of users to the same logon. once the logon exists, you don't need to throw the password around...is that the question?
Lowell
January 8, 2009 at 12:59 pm
yes. In my case the logon exists & when I try to create the user & execute as this new user to create db objects, the script as I detailed before throws errors. My question is whether after I create the user I need to link the user & login or automatically user is mapped to its corresponding login id?
January 8, 2009 at 3:24 pm
If you use the syntax that Lowell suggested i.e. "CREATE USER [bob] FOR LOGIN [bob]" the user bob is mapped to the login bob. Otherwise, you end up with an unmapped user.
Greg
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply