You need a mental overhaul if you think "sa" is a production-end-user
username (and password).
You NEVER give out the sa password. Only a handful of people should even
know what the sa password is.
First off, do you want to use sql authentication or windows integrated
authentication?
Second, bookmark
www.connectionstrings.com
Here is some code based on windows authentication.
Look up each of the "sp_" calls if you want to learn more.
And google "sql vs windows authentication" "sql server"
This is Sql Server 2005 code, but will probably run on 2000,2008 as well.
declare @databaseName varchar(64)
select @databaseName = db_name()
declare @currentLoginName varchar(64)
print '/@databaseName/'
print @databaseName
print ''
select @currentLoginName = 'mydomain\user1'
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\user2'
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\user3'
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\user4'
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\user5'
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\user6'
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\user1'
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\user2'
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\user3'
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\user4'
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\user5'
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_owner'
select @currentRole = 'db_datareader'
select @currentLoginName = 'mydomain\user2'
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\user3'
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\user4'
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\user5'
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\user6'
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\user1'
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