Re: Adding permissions to an AD user on a remote database
- From: Chad T <ChadT@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 2 Mar 2006 07:48:27 -0800
Thank you so much for your help Sue.
I was looking at the second record set for "sp_helplogins" and it has
multiple columns (Login Name, DB Name, UserName, UserorAlias) It appears
that when I run it the columns tell me: (UserID, Database Name, Permission
Type, Member/User)
Is that right?
"Sue Hoegemeier" wrote:
Okay...so you actually want to know if the login exists -.
not if it's a member of a database role. You can check if
the login exists before you add it using something like:
if not exists (select * from master.dbo.syslogins where
loginname = N'domain\userid')
exec sp_grantlogin N'domain\userid'
-Sue
On Tue, 28 Feb 2006 21:42:26 -0800, Chad T
<ChadT@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
I am having issues with IS_Member...
"Indicates whether the current user is a member of the specified Microsoft
Windows group or Microsoft SQL Server database role. "
I did find this:
sp_helplogins 'domain\userid'
The problem with this is that the records that I want are in the second
record set.
Is there a way I can loop through the second record set instead of the first
one?
"Sue Hoegemeier" wrote:
So the last piece you are looking for is the IS_MEMBER
function. That will tell you if a user is a member of the
specified database role.
-Sue
On Mon, 27 Feb 2006 20:47:26 -0800, Chad T
<ChadT@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
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..
- Follow-Ups:
- 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
- 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
- From: Chad T
- 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: how can i know
- Next by Date: Re: How to separate users in one database (MSSQL 2000)
- Previous by thread: Re: Adding permissions to an AD user on a remote database
- Next by thread: Re: Adding permissions to an AD user on a remote database
- Index(es):
Relevant Pages
|
