June 17, 2002 at 5:20 am
Every 2 months I do the same thing. create 3 new databases for the next phase of our datawarehouse.
Creating the users and permissions is a time-consuming, ad-hoc,and problematic step .
How can I script the users and permissions from the current phase to execute at the start of the next phase? I have sql accounts and win2K accounts I need.
-Kevin
June 17, 2002 at 5:51 am
Not sure what you're asking. How to come up with the scripts or how to apply them? If it's something you're doing that frequently I'd look at having a more complete script that generates the db, objects, users, etc.
Andy
June 17, 2002 at 5:54 am
We have scripts which create all the other objects. The only piece we don't have is users and permissions.
June 17, 2002 at 6:13 am
Using EM to generate the scripts there are three tabs. The last one "options" has the items you are looking for under the "Security Setting Options" area.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 17, 2002 at 6:17 am
I tried this. It doesn't do anything regarding NY accounts. Only yht sql accounts (nt is the majority of our authorization method)
-Kevin
June 17, 2002 at 6:40 am
Which options are you selecting as my NT accounts are being scripted just fine. This is the output on my test database.
if not exists (select * from dbo.sysusers where name = N'mb' and uid < 16382)
EXEC sp_grantdbaccess N'mb', N'mb'
GO
if not exists (select * from dbo.sysusers where name = N'TestMe' and uid < 16382)
EXEC sp_grantdbaccess N'TestMe', N'TestMe'
GO
--This is an NT account
if not exists (select * from dbo.sysusers where name = N'BOG_Editor' and uid < 16382)
EXEC sp_grantdbaccess N'CH\BOG_Editor', N'BOG_Editor'
GO
if not exists (select * from dbo.sysusers where name = N'ProcExec' and uid > 16399)
EXEC sp_addrole N'ProcExec'
GO
if not exists (select * from dbo.sysusers where name = N'ViewTbl' and uid > 16399)
EXEC sp_addrole N'ViewTbl'
GO
--This is it being added to a role setup for stored procedure execution only.
exec sp_addrolemember N'ProcExec', N'BOG_Editor'
GO
exec sp_addrolemember N'ProcExec', N'ViewTbl'
GO
The Options I selected were "Script database users and database roles" and "Script object-level permissions"
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Edited by - antares686 on 06/17/2002 06:42:45 AM
June 17, 2002 at 7:14 am
Thankyou. I'm an idiot. I was unselecting the 'CREATE' and 'DROP' clauses.
-Kevin
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply