//Quote
It seems incredible to me that:
b) It would necessitate complex solutions like scripting the DB and/or
users, although I suppose worse things have happened. I really hope
there is a simpler solution.
//End Quote
Why is this "incredible " to you?
Having scripts for your database and database objects promotes repeatability
and success.
You can find a basic example here using sqlcmd.
http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!583.entry
Whether or not you use sqlcmd or not, you ~should have your database
scripts....especially if you want to guarantee repeatable results on a
client installation.
..................
Below is some more TSQL code for giving certain domain users the role of
'db_datareader'.
It adds the login, the username to the database and grants the role.
Again, its accomplished via code. If you want repeatable results, then code
it up.
http://www.pragprog.com/the-pragmatic-programmer/extracts/tips
Don't Use Manual Procedures
A shell script or batch file will execute the same instructions, in the same
order, time after time.
Don't Live with Broken Windows
Fix bad designs, wrong decisions, and poor code when you see them.
Don't Use Wizard Code You Don't Understand
Wizards can generate reams of code. Make sure you understand all of it
before you incorporate it into your project.
Use MyDatabase-- ON MyServer
GO
declare @databaseName varchar(64)
select @databaseName = db_name()
declare @currentLoginName varchar(64)
print '/@databaseName/'
print @databaseName
print ''
select @currentLoginName = 'mydomain\myuser1'
if not exists (select null from master.dbo.syslogins where isntname = 1 and
loginname = @currentLoginName )
begin
EXEC sp_grantlogin @loginame = @currentLoginName
EXEC sp_defaultdb @loginame = @currentLoginName , @defdb = @databaseName
end
select @currentLoginName = 'mydomain\myuser2'
if not exists (select null from master.dbo.syslogins where isntname = 1 and
loginname = @currentLoginName )
begin
EXEC sp_grantlogin @loginame = @currentLoginName
EXEC sp_defaultdb @loginame = @currentLoginName , @defdb = @databaseName
end
--==========================
declare @sp_grantdbaccess_return_value int
declare @sp_grantdbaccess_return_value_total int
select @sp_grantdbaccess_return_value_total = 0
-- declare @currentLoginName varchar(64)
select @currentLoginName = 'mydomain\myuser1'
if not exists (select * from master.dbo.syslogins ml inner join dbo.sysusers
su on ml.sid = su.sid where ml.name = @currentLoginName and su.uid <
6382 )
begin
exec @sp_grantdbaccess_return_value = sp_grantdbaccess @currentLoginName
select @sp_grantdbaccess_return_value_total =
@sp_grantdbaccess_return_value_total + @sp_grantdbaccess_return_value
end
select @currentLoginName = 'mydomain\myuser2'
if not exists (select * from master.dbo.syslogins ml inner join dbo.sysusers
su on ml.sid = su.sid where ml.name = @currentLoginName and su.uid <
6382 )
begin
exec @sp_grantdbaccess_return_value = sp_grantdbaccess @currentLoginName
select @sp_grantdbaccess_return_value_total =
@sp_grantdbaccess_return_value_total + @sp_grantdbaccess_return_value
end
print '/@sp_grantdbaccess_return_value_total/'
print @sp_grantdbaccess_return_value_total
-- declare @currentLoginName varchar(64)
declare @sp_addrolemember_return_value int
declare @sp_addrolemember_return_value_total int
select @sp_addrolemember_return_value_total = 0
declare @currentRole varchar(64)
select @currentRole = 'db_datareader'
select @currentLoginName = 'mydomain\myuser2'
if not exists
(
SELECT null --, role_principal_id, member_principal_id, princ1.name AS
role_name, princ2.name As member_name
FROM sys.database_role_members AS S LEFT JOIN sys.database_principals AS
princ1 ON S.role_principal_id = princ1.principal_id AND princ1.type = 'R'
LEFT JOIN sys.database_principals AS princ2 ON S.member_principal_id =
princ2.principal_id AND princ2.type IN ('S', 'U')
WHERE princ1.name = @currentRole and princ2.name = @currentLoginName
)
begin
exec sp_addrolemember @currentRole, @currentLoginName
end
select @currentLoginName = 'mydomain\myuser1'
if not exists
(
SELECT null --, role_principal_id, member_principal_id, princ1.name AS
role_name, princ2.name As member_name
FROM sys.database_role_members AS S LEFT JOIN sys.database_principals AS
princ1 ON S.role_principal_id = princ1.principal_id AND princ1.type = 'R'
LEFT JOIN sys.database_principals AS princ2 ON S.member_principal_id =
princ2.principal_id AND princ2.type IN ('S', 'U')
WHERE princ1.name = @currentRole and princ2.name = @currentLoginName
)
begin
exec sp_addrolemember @currentRole, @currentLoginName
end
print '/@sp_addrolemember_return_value_total/'
print @sp_addrolemember_return_value_total
