I was wondering if someone can point me in the right direction. I am lookin
g
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..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@.discussions.microsoft.com> wrote:
>I was wondering if someone can point me in the right direction. I am looki
ng
>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..|||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 &"]" & vbCr
Lf
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@.discussions.microsoft.com> 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@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>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 &"]" & vbC
rLf
>conn.execute(m)
>Conn.close
>set conn = nothing
>"Sue Hoegemeier" 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@.discussions.microsoft.com> 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@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>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 firs
t
>one?
>
>"Sue Hoegemeier" wrote:
>|||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@.discussions.microsoft.com> wrote:
>
>|||If you are executing this in query analyzer, there will be
two results in the results pane. You need to use the scroll
for the results pane to see the other set of results.
-Sue
On Thu, 2 Mar 2006 07:48:27 -0800, Chad T
<ChadT@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>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:
>
For more information about sa, take a look at: