Re: Adding permissions to an AD user on a remote database
- From: Chad T <ChadT@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 27 Feb 2006 20:47:26 -0800
Just to clarify... the 400 workstations have sql running on it for an
application that has an offline mode...
The previous post mentioned to use: sp_grantlogin, sp_grantdbaccess,
sp_addrolemember which got me to here <see working code below>
How can I verify or check to see a user is already a member of the role ONE
database?
Example: If I am trying to add a user to the 'db_owner' role on the DB1
database and they are already listed as a member I would like to skip the
sp_addrolemember command.
This is all I could find:
IF SUSER_SID('domain\userid') IS NULL begin
but this only seems to find out if the user has a sid, not if the user is
listed in the DB1 database as a db_owner
Any help would be greatly appreciated.
This is my code so far....
'++++++++++++++++++++++++++++++++++++++
Domain = "DDDDDDDD"
Userid = "HHHHHHHH"
RemoteSQL = "WWW"
full_login = domain & "\" & userid
strconn = "Provider='SQLOLEDB'; Data Source='"&remotesql&"'; Initial
Catalog='master'; User Id='XXXXXX'; Password='????????';"
Set conn = CreateObject("adodb.connection")
conn.Open strconn
m = m & "USE DB0" & vbCrLf
m = m & "EXEC sp_grantlogin '"& full_login &"'" & vbCrLf
m = m & "EXEC sp_grantdbaccess '"& full_login &"', '"& full_login &"'" &
vbCrLf
m = m & "USE DB1" & vbCrLf
m = m & "EXEC sp_grantdbaccess '"& full_login &"', '"& full_login &"'" &
vbCrLf
m = m & "EXEC sp_addrolemember 'db_owner', '"& full_login &"'" & vbCrLf
m = m & "USE DB2" & vbCrLf
m = m & "EXEC sp_grantdbaccess '"& full_login &"', '"& full_login &"'" &
vbCrLf
m = m & "EXEC sp_addrolemember 'db_owner', '"& full_login &"'" & vbCrLf
'for the store procedure
m = m & "Use DB3" & vbCrLf
m = m & "GRANT EXECUTE ON CustOrdersOrders TO ["& full_login &"]" & vbCrLf
conn.execute(m)
Conn.close
set conn = nothing
"Sue Hoegemeier" wrote:
I'm not sure what you mean by 400 workstations - if a login.
needs access to databases and stored procedures, you would
set that at the server level in SQL Server and in whatever
databases.
Unless there is something in the application itself that has
to be coded which would be an application issue
If you are just trying to add the login and user to the
databases, you can script these using t-sql commands. See
books online topics for sp_grantlogin, sp_grantdbaccess,
sp_addrolemember.
-Sue
On Mon, 27 Feb 2006 13:11:27 -0800, Chad T
<ChadT@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
I was wondering if someone can point me in the right direction. I am looking
for a way to automate (in a script format) the addition of a user to a
database on a remote server. We have an application that requires that the
user have Owner permissions on 3 databases and 1 store procedure. Manually
connectiing to 400 workstations will be a huge pain..
- References:
- Re: Adding permissions to an AD user on a remote database
- From: Sue Hoegemeier
- Re: Adding permissions to an AD user on a remote database
- Prev by Date: Re: Adding permissions to an AD user on a remote database
- Next by Date: Re: Securing Data
- Previous by thread: Re: Adding permissions to an AD user on a remote database
- Index(es):
Relevant Pages
|
|
