I have a database and I want to grant an external user access to only one
view in my database. Using SQL Studio I generated these scripts and ran
them.
Once I did that I logged into the database as [loginExternalApp] and was
happy that I could not see any of the raw tables. But I found that I could
SELECT from all of the views in my database, not just the one I granted the
SELECT to.
What did I miss? I thought by default that the new role would have no
permissions for anything that I did not grant. It doesn't seem right that I
would need to specifically deny access to all of the views.
Any thoughts would be welcome.
Richard Speiss
Here is the script that I ran
USE [master]
GO
CREATE LOGIN [loginExternalApp] WITH PASSWORD=N'password',
DEFAULT_DATABASE=[TestDB], CHECK_EXPIRATION=OFF
GO
USE [TestDB]
GO
CREATE USER [userExternalApp] FOR LOGIN [loginExternalApp]
GO
CREATE ROLE [roleExternalApp]
GO
EXEC sp_addrolemember N'roleExternalApp', N'useExternalApp'
GO
GRANT SELECT ON [dbo].[vTestView] TO [roleExternalApp]
GO
Hi Richard,
Verify the permissions granted to the public role. Every user is a member of
the public role and has access to the objects granted to it.
Right-click any of these views in Management Studio, select Properties and
Permissions. See if permissions have been granted to public.
Also, you are not talking about catalog views, right?
Hope this helps,
Ben Nevarez
"Richard Speiss" wrote:
> I have a database and I want to grant an external user access to only one
> view in my database. Using SQL Studio I generated these scripts and ran
> them.
> Once I did that I logged into the database as [loginExternalApp] and was
> happy that I could not see any of the raw tables. But I found that I could
> SELECT from all of the views in my database, not just the one I granted the
> SELECT to.
> What did I miss? I thought by default that the new role would have no
> permissions for anything that I did not grant. It doesn't seem right that I
> would need to specifically deny access to all of the views.
> Any thoughts would be welcome.
> Richard Speiss
>
> Here is the script that I ran
> USE [master]
> GO
> CREATE LOGIN [loginExternalApp] WITH PASSWORD=N'password',
> DEFAULT_DATABASE=[TestDB], CHECK_EXPIRATION=OFF
> GO
> USE [TestDB]
> GO
> CREATE USER [userExternalApp] FOR LOGIN [loginExternalApp]
> GO
> CREATE ROLE [roleExternalApp]
> GO
> EXEC sp_addrolemember N'roleExternalApp', N'useExternalApp'
> GO
> GRANT SELECT ON [dbo].[vTestView] TO [roleExternalApp]
> GO
>
>
|||Thanks, those views are granting 'public' accessibility (user views, not
catalog views)
I don't see an easy way to disable the 'public' role though or to get my
[userExternalApp] to not be part of 'public'.
Is there any way to disable the public role or do I need to explicitly
revoke permissions to public on each view (and/or table, etc)?
Thanks again
Richard
"Ben Nevarez" <bnevarez@.no.spam.please.sunamerica.com> wrote in message
news:449C85C5-A8D8-49F5-8480-80584D1E91CD@.microsoft.com...[vbcol=seagreen]
> Hi Richard,
> Verify the permissions granted to the public role. Every user is a member
> of
> the public role and has access to the objects granted to it.
> Right-click any of these views in Management Studio, select Properties and
> Permissions. See if permissions have been granted to public.
> Also, you are not talking about catalog views, right?
> Hope this helps,
> Ben Nevarez
>
>
> "Richard Speiss" wrote:
Showing posts with label role. Show all posts
Showing posts with label role. Show all posts
Thursday, March 29, 2012
Adding a restricted role to SQL Server 2005
I have a database and I want to grant an external user access to only one
view in my database. Using SQL Studio I generated these scripts and ran
them.
Once I did that I logged into the database as [loginExternalApp] and was
happy that I could not see any of the raw tables. But I found that I could
SELECT from all of the views in my database, not just the one I granted the
SELECT to.
What did I miss? I thought by default that the new role would have no
permissions for anything that I did not grant. It doesn't seem right that I
would need to specifically deny access to all of the views.
Any thoughts would be welcome.
Richard Speiss
Here is the script that I ran
USE [master]
GO
CREATE LOGIN [loginExternalApp] WITH PASSWORD=N'password',
DEFAULT_DATABASE=[TestDB], CHECK_EXPIRATION=OFF
GO
USE [TestDB]
GO
CREATE USER [userExternalApp] FOR LOGIN [loginExternalApp]
GO
CREATE ROLE [roleExternalApp]
GO
EXEC sp_addrolemember N'roleExternalApp', N'useExternalApp'
GO
GRANT SELECT ON [dbo].[vTestView] TO [roleExternalApp]
GOHi Richard,
Verify the permissions granted to the public role. Every user is a member of
the public role and has access to the objects granted to it.
Right-click any of these views in Management Studio, select Properties and
Permissions. See if permissions have been granted to public.
Also, you are not talking about catalog views, right?
Hope this helps,
Ben Nevarez
"Richard Speiss" wrote:
> I have a database and I want to grant an external user access to only one
> view in my database. Using SQL Studio I generated these scripts and ran
> them.
> Once I did that I logged into the database as [loginExternalApp] and was
> happy that I could not see any of the raw tables. But I found that I could
> SELECT from all of the views in my database, not just the one I granted the
> SELECT to.
> What did I miss? I thought by default that the new role would have no
> permissions for anything that I did not grant. It doesn't seem right that I
> would need to specifically deny access to all of the views.
> Any thoughts would be welcome.
> Richard Speiss
>
> Here is the script that I ran
> USE [master]
> GO
> CREATE LOGIN [loginExternalApp] WITH PASSWORD=N'password',
> DEFAULT_DATABASE=[TestDB], CHECK_EXPIRATION=OFF
> GO
> USE [TestDB]
> GO
> CREATE USER [userExternalApp] FOR LOGIN [loginExternalApp]
> GO
> CREATE ROLE [roleExternalApp]
> GO
> EXEC sp_addrolemember N'roleExternalApp', N'useExternalApp'
> GO
> GRANT SELECT ON [dbo].[vTestView] TO [roleExternalApp]
> GO
>
>|||Thanks, those views are granting 'public' accessibility (user views, not
catalog views)
I don't see an easy way to disable the 'public' role though or to get my
[userExternalApp] to not be part of 'public'.
Is there any way to disable the public role or do I need to explicitly
revoke permissions to public on each view (and/or table, etc)?
Thanks again
Richard
"Ben Nevarez" <bnevarez@.no.spam.please.sunamerica.com> wrote in message
news:449C85C5-A8D8-49F5-8480-80584D1E91CD@.microsoft.com...
> Hi Richard,
> Verify the permissions granted to the public role. Every user is a member
> of
> the public role and has access to the objects granted to it.
> Right-click any of these views in Management Studio, select Properties and
> Permissions. See if permissions have been granted to public.
> Also, you are not talking about catalog views, right?
> Hope this helps,
> Ben Nevarez
>
>
> "Richard Speiss" wrote:
>> I have a database and I want to grant an external user access to only one
>> view in my database. Using SQL Studio I generated these scripts and ran
>> them.
>> Once I did that I logged into the database as [loginExternalApp] and was
>> happy that I could not see any of the raw tables. But I found that I
>> could
>> SELECT from all of the views in my database, not just the one I granted
>> the
>> SELECT to.
>> What did I miss? I thought by default that the new role would have no
>> permissions for anything that I did not grant. It doesn't seem right
>> that I
>> would need to specifically deny access to all of the views.
>> Any thoughts would be welcome.
>> Richard Speiss
>>
>> Here is the script that I ran
>> USE [master]
>> GO
>> CREATE LOGIN [loginExternalApp] WITH PASSWORD=N'password',
>> DEFAULT_DATABASE=[TestDB], CHECK_EXPIRATION=OFF
>> GO
>> USE [TestDB]
>> GO
>> CREATE USER [userExternalApp] FOR LOGIN [loginExternalApp]
>> GO
>> CREATE ROLE [roleExternalApp]
>> GO
>> EXEC sp_addrolemember N'roleExternalApp', N'useExternalApp'
>> GO
>> GRANT SELECT ON [dbo].[vTestView] TO [roleExternalApp]
>> GO
>>
view in my database. Using SQL Studio I generated these scripts and ran
them.
Once I did that I logged into the database as [loginExternalApp] and was
happy that I could not see any of the raw tables. But I found that I could
SELECT from all of the views in my database, not just the one I granted the
SELECT to.
What did I miss? I thought by default that the new role would have no
permissions for anything that I did not grant. It doesn't seem right that I
would need to specifically deny access to all of the views.
Any thoughts would be welcome.
Richard Speiss
Here is the script that I ran
USE [master]
GO
CREATE LOGIN [loginExternalApp] WITH PASSWORD=N'password',
DEFAULT_DATABASE=[TestDB], CHECK_EXPIRATION=OFF
GO
USE [TestDB]
GO
CREATE USER [userExternalApp] FOR LOGIN [loginExternalApp]
GO
CREATE ROLE [roleExternalApp]
GO
EXEC sp_addrolemember N'roleExternalApp', N'useExternalApp'
GO
GRANT SELECT ON [dbo].[vTestView] TO [roleExternalApp]
GOHi Richard,
Verify the permissions granted to the public role. Every user is a member of
the public role and has access to the objects granted to it.
Right-click any of these views in Management Studio, select Properties and
Permissions. See if permissions have been granted to public.
Also, you are not talking about catalog views, right?
Hope this helps,
Ben Nevarez
"Richard Speiss" wrote:
> I have a database and I want to grant an external user access to only one
> view in my database. Using SQL Studio I generated these scripts and ran
> them.
> Once I did that I logged into the database as [loginExternalApp] and was
> happy that I could not see any of the raw tables. But I found that I could
> SELECT from all of the views in my database, not just the one I granted the
> SELECT to.
> What did I miss? I thought by default that the new role would have no
> permissions for anything that I did not grant. It doesn't seem right that I
> would need to specifically deny access to all of the views.
> Any thoughts would be welcome.
> Richard Speiss
>
> Here is the script that I ran
> USE [master]
> GO
> CREATE LOGIN [loginExternalApp] WITH PASSWORD=N'password',
> DEFAULT_DATABASE=[TestDB], CHECK_EXPIRATION=OFF
> GO
> USE [TestDB]
> GO
> CREATE USER [userExternalApp] FOR LOGIN [loginExternalApp]
> GO
> CREATE ROLE [roleExternalApp]
> GO
> EXEC sp_addrolemember N'roleExternalApp', N'useExternalApp'
> GO
> GRANT SELECT ON [dbo].[vTestView] TO [roleExternalApp]
> GO
>
>|||Thanks, those views are granting 'public' accessibility (user views, not
catalog views)
I don't see an easy way to disable the 'public' role though or to get my
[userExternalApp] to not be part of 'public'.
Is there any way to disable the public role or do I need to explicitly
revoke permissions to public on each view (and/or table, etc)?
Thanks again
Richard
"Ben Nevarez" <bnevarez@.no.spam.please.sunamerica.com> wrote in message
news:449C85C5-A8D8-49F5-8480-80584D1E91CD@.microsoft.com...
> Hi Richard,
> Verify the permissions granted to the public role. Every user is a member
> of
> the public role and has access to the objects granted to it.
> Right-click any of these views in Management Studio, select Properties and
> Permissions. See if permissions have been granted to public.
> Also, you are not talking about catalog views, right?
> Hope this helps,
> Ben Nevarez
>
>
> "Richard Speiss" wrote:
>> I have a database and I want to grant an external user access to only one
>> view in my database. Using SQL Studio I generated these scripts and ran
>> them.
>> Once I did that I logged into the database as [loginExternalApp] and was
>> happy that I could not see any of the raw tables. But I found that I
>> could
>> SELECT from all of the views in my database, not just the one I granted
>> the
>> SELECT to.
>> What did I miss? I thought by default that the new role would have no
>> permissions for anything that I did not grant. It doesn't seem right
>> that I
>> would need to specifically deny access to all of the views.
>> Any thoughts would be welcome.
>> Richard Speiss
>>
>> Here is the script that I ran
>> USE [master]
>> GO
>> CREATE LOGIN [loginExternalApp] WITH PASSWORD=N'password',
>> DEFAULT_DATABASE=[TestDB], CHECK_EXPIRATION=OFF
>> GO
>> USE [TestDB]
>> GO
>> CREATE USER [userExternalApp] FOR LOGIN [loginExternalApp]
>> GO
>> CREATE ROLE [roleExternalApp]
>> GO
>> EXEC sp_addrolemember N'roleExternalApp', N'useExternalApp'
>> GO
>> GRANT SELECT ON [dbo].[vTestView] TO [roleExternalApp]
>> GO
>>
Monday, March 19, 2012
add user to role with WMI
How to add a win32 user defined in the sql server to a User defined role wit
h
WMI for sql server
Guess MSSQL_MemberUser would do the trick, but how ? Can someone post a
quick example
Mydomain\User1 as username
My_Role_Read as role name
Win srv 2003 standard
Sql srv 2000 standard with WMI option installed
Thx
MarcFound the solution to my problem thought I'd share
' MyDB is the
target DB
objRoleName = "My_role_MyDB_r" ' Role defined in MyDB
ObjUserName = "NewUser" ' User defined in MyDB
cmdobj = "sp_addrolemember " ' in case I need looping with
variables later on
Set objLocator = CreateObject("wbemscripting.swbemlocator")
' Connect to correct NS
Set ObjServices = ObjLocator.ConnectServer(".", "root\microsoftsqlserver")
' Integrated security version
ObjServices.security_.impersonationlevel = 3
' What DB was that again
set objService2 =
objServices.Get("MSSQL_Database.SQLServerName='(LOCAL)',Name='MyDB'")
' Make the cmd usefull
cmdobj = cmdobj & "'" & objRoleName & "', '" & ObjUserName & "'"
' Let us not wait anylonger do it
objService2.executeimmediate(cmdobj)
Hope this will help someone else
Marc
h
WMI for sql server
Guess MSSQL_MemberUser would do the trick, but how ? Can someone post a
quick example
Mydomain\User1 as username
My_Role_Read as role name
Win srv 2003 standard
Sql srv 2000 standard with WMI option installed
Thx
MarcFound the solution to my problem thought I'd share
' MyDB is the
target DB
objRoleName = "My_role_MyDB_r" ' Role defined in MyDB
ObjUserName = "NewUser" ' User defined in MyDB
cmdobj = "sp_addrolemember " ' in case I need looping with
variables later on
Set objLocator = CreateObject("wbemscripting.swbemlocator")
' Connect to correct NS
Set ObjServices = ObjLocator.ConnectServer(".", "root\microsoftsqlserver")
' Integrated security version
ObjServices.security_.impersonationlevel = 3
' What DB was that again
set objService2 =
objServices.Get("MSSQL_Database.SQLServerName='(LOCAL)',Name='MyDB'")
' Make the cmd usefull
cmdobj = cmdobj & "'" & objRoleName & "', '" & ObjUserName & "'"
' Let us not wait anylonger do it
objService2.executeimmediate(cmdobj)
Hope this will help someone else
Marc
add user to database role
Hello,
When i add a user to a database role, enterprise manager
ask me for the user password. I do the same in other
server and and i don't need to give any password.
I've yet the same problem but i cant remember what i've
done to correct this situation.
Best Regards
Hi;
Not sure about this issue. I recommend you to add the server / database
fixed roles
from query analyzer.
For the Server wide roles use :-
sp_addsrvrolemember 'login','sysadmin'
For the Database wide roles use:-
use paytest
go
sp_addrolemember 'db_owner','user'
Thanks
Hari
MCDBA
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:18df601c44c75$c16bd250$a301280a@.phx.gbl...
> Hello,
> When i add a user to a database role, enterprise manager
> ask me for the user password. I do the same in other
> server and and i don't need to give any password.
> I've yet the same problem but i cant remember what i've
> done to correct this situation.
> Best Regards
|||You may be running into the following:
FIX: You Are Prompted for Password Confirmation After You
Change a Standard SQL Server Login
http://support.microsoft.com/?id=826161
-Sue
On Mon, 7 Jun 2004 02:56:51 -0700, "CC&JM"
<anonymous@.discussions.microsoft.com> wrote:
>Hello,
>When i add a user to a database role, enterprise manager
>ask me for the user password. I do the same in other
>server and and i don't need to give any password.
>I've yet the same problem but i cant remember what i've
>done to correct this situation.
>Best Regards
|||Thanks Sue
I apply the patch and now i've got no problem
Thanks again,
bye
>--Original Message--
>You may be running into the following:
>FIX: You Are Prompted for Password Confirmation After You
>Change a Standard SQL Server Login
>http://support.microsoft.com/?id=826161
>-Sue
>On Mon, 7 Jun 2004 02:56:51 -0700, "CC&JM"
><anonymous@.discussions.microsoft.com> wrote:
>
>.
>
When i add a user to a database role, enterprise manager
ask me for the user password. I do the same in other
server and and i don't need to give any password.
I've yet the same problem but i cant remember what i've
done to correct this situation.
Best Regards
Hi;
Not sure about this issue. I recommend you to add the server / database
fixed roles
from query analyzer.
For the Server wide roles use :-
sp_addsrvrolemember 'login','sysadmin'
For the Database wide roles use:-
use paytest
go
sp_addrolemember 'db_owner','user'
Thanks
Hari
MCDBA
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:18df601c44c75$c16bd250$a301280a@.phx.gbl...
> Hello,
> When i add a user to a database role, enterprise manager
> ask me for the user password. I do the same in other
> server and and i don't need to give any password.
> I've yet the same problem but i cant remember what i've
> done to correct this situation.
> Best Regards
|||You may be running into the following:
FIX: You Are Prompted for Password Confirmation After You
Change a Standard SQL Server Login
http://support.microsoft.com/?id=826161
-Sue
On Mon, 7 Jun 2004 02:56:51 -0700, "CC&JM"
<anonymous@.discussions.microsoft.com> wrote:
>Hello,
>When i add a user to a database role, enterprise manager
>ask me for the user password. I do the same in other
>server and and i don't need to give any password.
>I've yet the same problem but i cant remember what i've
>done to correct this situation.
>Best Regards
|||Thanks Sue
I apply the patch and now i've got no problem
Thanks again,
bye
>--Original Message--
>You may be running into the following:
>FIX: You Are Prompted for Password Confirmation After You
>Change a Standard SQL Server Login
>http://support.microsoft.com/?id=826161
>-Sue
>On Mon, 7 Jun 2004 02:56:51 -0700, "CC&JM"
><anonymous@.discussions.microsoft.com> wrote:
>
>.
>
add user to database role
Hello,
When i add a user to a database role, enterprise manager
ask me for the user password. I do the same in other
server and and i don't need to give any password.
I've yet the same problem but i cant remember what i've
done to correct this situation.
Best RegardsHi;
Not sure about this issue. I recommend you to add the server / database
fixed roles
from query analyzer.
For the Server wide roles use :-
sp_addsrvrolemember 'login','sysadmin'
For the Database wide roles use:-
use paytest
go
sp_addrolemember 'db_owner','user'
Thanks
Hari
MCDBA
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:18df601c44c75$c16bd250$a301280a@.phx
.gbl...
> Hello,
> When i add a user to a database role, enterprise manager
> ask me for the user password. I do the same in other
> server and and i don't need to give any password.
> I've yet the same problem but i cant remember what i've
> done to correct this situation.
> Best Regards|||You may be running into the following:
FIX: You Are Prompted for Password Confirmation After You
Change a Standard SQL Server Login
http://support.microsoft.com/?id=826161
-Sue
On Mon, 7 Jun 2004 02:56:51 -0700, "CC&JM"
<anonymous@.discussions.microsoft.com> wrote:
>Hello,
>When i add a user to a database role, enterprise manager
>ask me for the user password. I do the same in other
>server and and i don't need to give any password.
>I've yet the same problem but i cant remember what i've
>done to correct this situation.
>Best Regards|||Thanks Sue
I apply the patch and now i've got no problem
Thanks again,
bye
>--Original Message--
>You may be running into the following:
>FIX: You Are Prompted for Password Confirmation After You
>Change a Standard SQL Server Login
>http://support.microsoft.com/?id=826161
>-Sue
>On Mon, 7 Jun 2004 02:56:51 -0700, "CC&JM"
><anonymous@.discussions.microsoft.com> wrote:
>
>.
>
When i add a user to a database role, enterprise manager
ask me for the user password. I do the same in other
server and and i don't need to give any password.
I've yet the same problem but i cant remember what i've
done to correct this situation.
Best RegardsHi;
Not sure about this issue. I recommend you to add the server / database
fixed roles
from query analyzer.
For the Server wide roles use :-
sp_addsrvrolemember 'login','sysadmin'
For the Database wide roles use:-
use paytest
go
sp_addrolemember 'db_owner','user'
Thanks
Hari
MCDBA
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:18df601c44c75$c16bd250$a301280a@.phx
.gbl...
> Hello,
> When i add a user to a database role, enterprise manager
> ask me for the user password. I do the same in other
> server and and i don't need to give any password.
> I've yet the same problem but i cant remember what i've
> done to correct this situation.
> Best Regards|||You may be running into the following:
FIX: You Are Prompted for Password Confirmation After You
Change a Standard SQL Server Login
http://support.microsoft.com/?id=826161
-Sue
On Mon, 7 Jun 2004 02:56:51 -0700, "CC&JM"
<anonymous@.discussions.microsoft.com> wrote:
>Hello,
>When i add a user to a database role, enterprise manager
>ask me for the user password. I do the same in other
>server and and i don't need to give any password.
>I've yet the same problem but i cant remember what i've
>done to correct this situation.
>Best Regards|||Thanks Sue
I apply the patch and now i've got no problem
Thanks again,
bye
>--Original Message--
>You may be running into the following:
>FIX: You Are Prompted for Password Confirmation After You
>Change a Standard SQL Server Login
>http://support.microsoft.com/?id=826161
>-Sue
>On Mon, 7 Jun 2004 02:56:51 -0700, "CC&JM"
><anonymous@.discussions.microsoft.com> wrote:
>
>.
>
add user to database role
Hello,
When i add a user to a database role, enterprise manager
ask me for the user password. I do the same in other
server and and i don't need to give any password.
I've yet the same problem but i cant remember what i've
done to correct this situation.
Best RegardsHi;
Not sure about this issue. I recommend you to add the server / database
fixed roles
from query analyzer.
For the Server wide roles use :-
sp_addsrvrolemember 'login','sysadmin'
For the Database wide roles use:-
use paytest
go
sp_addrolemember 'db_owner','user'
Thanks
Hari
MCDBA
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:18df601c44c75$c16bd250$a301280a@.phx.gbl...
> Hello,
> When i add a user to a database role, enterprise manager
> ask me for the user password. I do the same in other
> server and and i don't need to give any password.
> I've yet the same problem but i cant remember what i've
> done to correct this situation.
> Best Regards|||You may be running into the following:
FIX: You Are Prompted for Password Confirmation After You
Change a Standard SQL Server Login
http://support.microsoft.com/?id=826161
-Sue
On Mon, 7 Jun 2004 02:56:51 -0700, "CC&JM"
<anonymous@.discussions.microsoft.com> wrote:
>Hello,
>When i add a user to a database role, enterprise manager
>ask me for the user password. I do the same in other
>server and and i don't need to give any password.
>I've yet the same problem but i cant remember what i've
>done to correct this situation.
>Best Regards|||Thanks Sue
I apply the patch and now i've got no problem
Thanks again,
bye
>--Original Message--
>You may be running into the following:
>FIX: You Are Prompted for Password Confirmation After You
>Change a Standard SQL Server Login
>http://support.microsoft.com/?id=826161
>-Sue
>On Mon, 7 Jun 2004 02:56:51 -0700, "CC&JM"
><anonymous@.discussions.microsoft.com> wrote:
>>Hello,
>>When i add a user to a database role, enterprise manager
>>ask me for the user password. I do the same in other
>>server and and i don't need to give any password.
>>I've yet the same problem but i cant remember what i've
>>done to correct this situation.
>>Best Regards
>.
>
When i add a user to a database role, enterprise manager
ask me for the user password. I do the same in other
server and and i don't need to give any password.
I've yet the same problem but i cant remember what i've
done to correct this situation.
Best RegardsHi;
Not sure about this issue. I recommend you to add the server / database
fixed roles
from query analyzer.
For the Server wide roles use :-
sp_addsrvrolemember 'login','sysadmin'
For the Database wide roles use:-
use paytest
go
sp_addrolemember 'db_owner','user'
Thanks
Hari
MCDBA
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:18df601c44c75$c16bd250$a301280a@.phx.gbl...
> Hello,
> When i add a user to a database role, enterprise manager
> ask me for the user password. I do the same in other
> server and and i don't need to give any password.
> I've yet the same problem but i cant remember what i've
> done to correct this situation.
> Best Regards|||You may be running into the following:
FIX: You Are Prompted for Password Confirmation After You
Change a Standard SQL Server Login
http://support.microsoft.com/?id=826161
-Sue
On Mon, 7 Jun 2004 02:56:51 -0700, "CC&JM"
<anonymous@.discussions.microsoft.com> wrote:
>Hello,
>When i add a user to a database role, enterprise manager
>ask me for the user password. I do the same in other
>server and and i don't need to give any password.
>I've yet the same problem but i cant remember what i've
>done to correct this situation.
>Best Regards|||Thanks Sue
I apply the patch and now i've got no problem
Thanks again,
bye
>--Original Message--
>You may be running into the following:
>FIX: You Are Prompted for Password Confirmation After You
>Change a Standard SQL Server Login
>http://support.microsoft.com/?id=826161
>-Sue
>On Mon, 7 Jun 2004 02:56:51 -0700, "CC&JM"
><anonymous@.discussions.microsoft.com> wrote:
>>Hello,
>>When i add a user to a database role, enterprise manager
>>ask me for the user password. I do the same in other
>>server and and i don't need to give any password.
>>I've yet the same problem but i cant remember what i've
>>done to correct this situation.
>>Best Regards
>.
>
Sunday, March 11, 2012
Add role to Analysis Services Database
Does anyone know if there is a way to use a SSIS task to add a role to SSAS cube? At the moment I use Management Studio, Right Click "Roles" under the cube and go through New Role wizard, but I'd like to be able to include this in a SSIS package instead.
Thanks
Richard
You can do it through the Script task, employing the AMO (Analysis Management Objects) library. I recommend creating the role in SSMS first but instead of submitting the change to the system, clicking the Script button at the top of the New Role dialog box to view the XMLA\ASSL script it creates. The script can be a guide for your AMO development (as AMO is just a wrapper for the XMLA\ASSL languages).
Good luck,
Bryan
|||Following along with what Bryan had mentioned you could script a role using Management Studio to use as a template then use and expression variable and an Analysis Services Execute DDL Task in SSIS to accomplish this as an ongoing solution. The Analysis Services Execute DDL Task will allow you to customize and execute the role creation/modification command and avoid having to use the script task.
Hope that helps!
|||Thanks! Wow. Cool. Kind of got it to work but don't know quite how!
In SSMS scripting the role worked nicely and was able to run it in a query window and it did exactly what was needed.
I'm pretty useless at VB.NET so trying the Script Task in SSIS, I hit design script, added AMO as a reference and pasted the SSMS script where it says "Add your code here" inside of Public Sub Main(). Of course it doesn't work that simply. But is there a really simple answer to what I need or am I going to have to go off and learn some VB.NET ?
So then I tried Execute DDL task, and muddled my way around it. I save the original script as a .xmla file in the file system. Then in DDL task under DDL selected File Connection as SourceType and connected to this file. Executed task and it did what was needed.
I guess this is good enough as it works, but I wouldn't mind understanding better what I am doing!!
Thanks
Richard
|||Here is a code sample from a past project where we created a role for each entry in a table. (Just sharing that last part so you understand the database query in the code.)
Code Snippet
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.AnalysisServices
Public Class ScriptMain
Public Sub Main()
Dim server As New Microsoft.AnalysisServices.Server
Try
' CONNECT TO THE SQL SERVER ANALYSIS SERVICES (SSAS) SERVER
server.Connect("localhost")
' THE SSAS DATABASE TO CONNECT TO
Dim database As New Microsoft.AnalysisServices.Database
database = server.Databases.FindByName(Dts.Variables("AnalysisServicesDatabaseName").Value.ToString)
' GET A LIST OF ROLES TO CREATE
' Connection String comes from the 'METADATA' Connection Manager
Dim myConnection As New SqlConnection(Dts.Connections.Item(0).ConnectionString.ToString())
Dim myCommand As SqlCommand = New SqlCommand("SELECT ID FROM dbo.Roles (NOLOCK) WHERE ID > 0", myConnection)
myConnection.Open()
' FILL THE DATAREADER
Dim dr As SqlDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
' LOOP THROUGH THE DATAREADER AND BUILD A ROLE
If (dr.HasRows) Then
While (dr.Read())
CreateRole("Role for ID ", dr("ID").ToString, database)
End While
End If
' CLOSE THE DATAREADER
dr.Close()
' CLOSE THE SQL SERVER DB CONNECTION
myConnection.Close()
Dts.TaskResult = Dts.Results.Success
Catch ex As Exception
Dts.Events.FireError(1, ex.TargetSite.ToString, ex.Message, "", 0)
Finally
' DISCONNECT THE SSAS SERVER
server.Disconnect()
End Try
Dts.TaskResult = Dts.Results.Success
End Sub
Private Sub CreateRole(ByVal rolePrefix As String, ByVal dsNumber As String, ByVal asDatabase As Database)
Try
' WILL CREATE A ROLE IN THE ASSIGNED DB WITH THE NAME AND KEY VALUE
Dim newRole As Role
' CREATE THE NEW ROLES NAME
newRole = asDatabase.Roles.Add(rolePrefix + " " + dsNumber)
newRole.Description = "Role for " + rolePrefix + " " + dsNumber
newRole.Update()
Catch ex As Exception
Throw New Exception(ex.Message.ToString())
End Try
End Sub
End Class
Tuesday, March 6, 2012
Add many users to Reporting Services
I am looking for a script, query or utility that can add many user's at a
time to a role assignemnt in reporting services.
In the gui - I have tried space delimited, comma delimited... and always get
an error.
trying to add
domain\user1 domain\user2...
ThanksThe easiest way to manage this is by using Windows groups.
You can add windows groups to roles in reporting services.
-Sue
On Fri, 25 May 2007 12:35:00 -0700, Brett S.
<BrettS@.discussions.microsoft.com> wrote:
>I am looking for a script, query or utility that can add many user's at a
>time to a role assignemnt in reporting services.
>In the gui - I have tried space delimited, comma delimited... and always ge
t
>an error.
>trying to add
>domain\user1 domain\user2...
>Thanks
time to a role assignemnt in reporting services.
In the gui - I have tried space delimited, comma delimited... and always get
an error.
trying to add
domain\user1 domain\user2...
ThanksThe easiest way to manage this is by using Windows groups.
You can add windows groups to roles in reporting services.
-Sue
On Fri, 25 May 2007 12:35:00 -0700, Brett S.
<BrettS@.discussions.microsoft.com> wrote:
>I am looking for a script, query or utility that can add many user's at a
>time to a role assignemnt in reporting services.
>In the gui - I have tried space delimited, comma delimited... and always ge
t
>an error.
>trying to add
>domain\user1 domain\user2...
>Thanks
Add Login
I want to add login in SQL Server 2005 through Query Analyzer and grant him
System Administrative Role through query analyzer, can any one help me out?
hi,
that is quite easy, according to SQl Server 2005 this is:
CREATE LOGIN SomeUser WITH PASSWORD = 'SomePassword'
sp_addsrvrolemember 'SomeUser','sysadmin'
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
System Administrative Role through query analyzer, can any one help me out?
hi,
that is quite easy, according to SQl Server 2005 this is:
CREATE LOGIN SomeUser WITH PASSWORD = 'SomePassword'
sp_addsrvrolemember 'SomeUser','sysadmin'
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
Saturday, February 25, 2012
Add group or user name..Bug or not?
Hi.. I just want to know if anybody else has experienced this problem.
When i try to add group [xx\yyyYYYY Datakontakter] to a role on a folder the
response is "The user or group name 'xxYYYY Datakontakter' is not recognized.
(rsUnknownUserName) ". However, when it is [xx\xxxYYYY Filialchefer] it is
ok.
The difference are that the first one have 21 characters after the \ and the
second one 21. To make it even more interesting if i try to add the second
one with its [pre-2000] name, which is [xxxYYYY Region-1] it is ok. Now, i
havent yet tested if it really works after adding the group, so i´m not in
the clear yet. Anybody else experienced the same problem?It should be:
The difference are that the first one have 21 characters and the second one
20. To make it even more interesting if i try to add the FIRST one with its
[pre-2000] name, which is [xxxYYYY Region-1] it is ok.
When i try to add group [xx\yyyYYYY Datakontakter] to a role on a folder the
response is "The user or group name 'xxYYYY Datakontakter' is not recognized.
(rsUnknownUserName) ". However, when it is [xx\xxxYYYY Filialchefer] it is
ok.
The difference are that the first one have 21 characters after the \ and the
second one 21. To make it even more interesting if i try to add the second
one with its [pre-2000] name, which is [xxxYYYY Region-1] it is ok. Now, i
havent yet tested if it really works after adding the group, so i´m not in
the clear yet. Anybody else experienced the same problem?It should be:
The difference are that the first one have 21 characters and the second one
20. To make it even more interesting if i try to add the FIRST one with its
[pre-2000] name, which is [xxxYYYY Region-1] it is ok.
Friday, February 24, 2012
Add domain user?
Hi,
By default, when add a domain user in the Domain Controller, is it this new
user can access the sql data?
public role only? Thanks...By default, a new AD user will have no access to a specific SQL Server
unless they are added to a windows group that has access to SQL Server or
their domain account is explicitly added as a login.
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"js" <js@.someone@.hotmail.com> wrote in message
news:uB%23HnU3MEHA.3712@.TK2MSFTNGP10.phx.gbl...
> Hi,
> By default, when add a domain user in the Domain Controller, is it this
new
> user can access the sql data?
> public role only? Thanks...
>|||Thanks Jasper.
Another question about this one: if a particular role of which the user is a
member has been denied a specific object permission (such as SELECT), the
user is unable to exercise that permission. The most restrictive permission
(DENY) takes precedence.
What will happen in this case: MYDN\Test is belonging to local administrator
group and System Admin Server Role. And I set "db_denydatawriter" to this
user in Northwind database. Can this user exec the "Updte ." statement in
Northwind's table?
Please advice...
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:OSzDVD5MEHA.2592@.tk2msftngp13.phx.gbl...
> By default, a new AD user will have no access to a specific SQL Server
> unless they are added to a windows group that has access to SQL Server or
> their domain account is explicitly added as a login.
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "js" <js@.someone@.hotmail.com> wrote in message
> news:uB%23HnU3MEHA.3712@.TK2MSFTNGP10.phx.gbl...
> new
>|||SA will overrule. The user will be SA.
Jeff Duncan
MCDBA, MCSE+I
"js" <js@.someone@.hotmail.com> wrote in message
news:edWLIh5MEHA.1644@.TK2MSFTNGP09.phx.gbl...
> Thanks Jasper.
> Another question about this one: if a particular role of which the user is
> a
> member has been denied a specific object permission (such as SELECT), the
> user is unable to exercise that permission. The most restrictive
> permission
> (DENY) takes precedence.
> What will happen in this case: MYDN\Test is belonging to local
> administrator
> group and System Admin Server Role. And I set "db_denydatawriter" to this
> user in Northwind database. Can this user exec the "Updte ." statement in
> Northwind's table?
>
> Please advice...
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:OSzDVD5MEHA.2592@.tk2msftngp13.phx.gbl...
>|||Thanks Jeff.
How to assign denywrite to MYDN\Test have without by removing it from local
administrator?
If I take out the db_owner from BUILTIN\Administrators will cause any
problem'
"Jeff Duncan" <jduncan@.gtefcu.org> wrote in message
news:eq5wXr5MEHA.3208@.TK2MSFTNGP10.phx.gbl...
> SA will overrule. The user will be SA.
> --
> Jeff Duncan
> MCDBA, MCSE+I
> "js" <js@.someone@.hotmail.com> wrote in message
> news:edWLIh5MEHA.1644@.TK2MSFTNGP09.phx.gbl...
is[vbcol=seagreen]
the[vbcol=seagreen]
this[vbcol=seagreen]
in[vbcol=seagreen]
or[vbcol=seagreen]
>|||Go through it first with a fine tooth comb. Make sure the ID that is
running SQL has access by itself and not in a group. Make sure that the
proper Local Admins have direct access accounts and then remove it. Make
yourself a standard login first just for safe keeping if you are in mixed
mode. You should be fine.
Jeff Duncan
MCDBA, MCSE+I
"js" <js@.someone@.hotmail.com> wrote in message
news:O86FNw5MEHA.3472@.TK2MSFTNGP10.phx.gbl...
> Thanks Jeff.
> How to assign denywrite to MYDN\Test have without by removing it from
> local
> administrator?
> If I take out the db_owner from BUILTIN\Administrators will cause any
> problem'
>
>
> "Jeff Duncan" <jduncan@.gtefcu.org> wrote in message
> news:eq5wXr5MEHA.3208@.TK2MSFTNGP10.phx.gbl...
> is
> the
> this
> in
> or
>
By default, when add a domain user in the Domain Controller, is it this new
user can access the sql data?
public role only? Thanks...By default, a new AD user will have no access to a specific SQL Server
unless they are added to a windows group that has access to SQL Server or
their domain account is explicitly added as a login.
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"js" <js@.someone@.hotmail.com> wrote in message
news:uB%23HnU3MEHA.3712@.TK2MSFTNGP10.phx.gbl...
> Hi,
> By default, when add a domain user in the Domain Controller, is it this
new
> user can access the sql data?
> public role only? Thanks...
>|||Thanks Jasper.
Another question about this one: if a particular role of which the user is a
member has been denied a specific object permission (such as SELECT), the
user is unable to exercise that permission. The most restrictive permission
(DENY) takes precedence.
What will happen in this case: MYDN\Test is belonging to local administrator
group and System Admin Server Role. And I set "db_denydatawriter" to this
user in Northwind database. Can this user exec the "Updte ." statement in
Northwind's table?
Please advice...
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:OSzDVD5MEHA.2592@.tk2msftngp13.phx.gbl...
> By default, a new AD user will have no access to a specific SQL Server
> unless they are added to a windows group that has access to SQL Server or
> their domain account is explicitly added as a login.
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "js" <js@.someone@.hotmail.com> wrote in message
> news:uB%23HnU3MEHA.3712@.TK2MSFTNGP10.phx.gbl...
> new
>|||SA will overrule. The user will be SA.
Jeff Duncan
MCDBA, MCSE+I
"js" <js@.someone@.hotmail.com> wrote in message
news:edWLIh5MEHA.1644@.TK2MSFTNGP09.phx.gbl...
> Thanks Jasper.
> Another question about this one: if a particular role of which the user is
> a
> member has been denied a specific object permission (such as SELECT), the
> user is unable to exercise that permission. The most restrictive
> permission
> (DENY) takes precedence.
> What will happen in this case: MYDN\Test is belonging to local
> administrator
> group and System Admin Server Role. And I set "db_denydatawriter" to this
> user in Northwind database. Can this user exec the "Updte ." statement in
> Northwind's table?
>
> Please advice...
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:OSzDVD5MEHA.2592@.tk2msftngp13.phx.gbl...
>|||Thanks Jeff.
How to assign denywrite to MYDN\Test have without by removing it from local
administrator?
If I take out the db_owner from BUILTIN\Administrators will cause any
problem'
"Jeff Duncan" <jduncan@.gtefcu.org> wrote in message
news:eq5wXr5MEHA.3208@.TK2MSFTNGP10.phx.gbl...
> SA will overrule. The user will be SA.
> --
> Jeff Duncan
> MCDBA, MCSE+I
> "js" <js@.someone@.hotmail.com> wrote in message
> news:edWLIh5MEHA.1644@.TK2MSFTNGP09.phx.gbl...
is[vbcol=seagreen]
the[vbcol=seagreen]
this[vbcol=seagreen]
in[vbcol=seagreen]
or[vbcol=seagreen]
>|||Go through it first with a fine tooth comb. Make sure the ID that is
running SQL has access by itself and not in a group. Make sure that the
proper Local Admins have direct access accounts and then remove it. Make
yourself a standard login first just for safe keeping if you are in mixed
mode. You should be fine.
Jeff Duncan
MCDBA, MCSE+I
"js" <js@.someone@.hotmail.com> wrote in message
news:O86FNw5MEHA.3472@.TK2MSFTNGP10.phx.gbl...
> Thanks Jeff.
> How to assign denywrite to MYDN\Test have without by removing it from
> local
> administrator?
> If I take out the db_owner from BUILTIN\Administrators will cause any
> problem'
>
>
> "Jeff Duncan" <jduncan@.gtefcu.org> wrote in message
> news:eq5wXr5MEHA.3208@.TK2MSFTNGP10.phx.gbl...
> is
> the
> this
> in
> or
>
Add Database Role to Login Name
I have a SQL Server 2000 database that I restore often.
After the database is restored I need to add database security role to Login
Name Chicago\CH-datareader-K.
How would I add the database role 'db_datareader' to Login Name
Chicago\CH-datareader-K that correponds to User Name CH-datareader-K.
Please help me create the correct syntax so I add this to my restore script.
Thanks,Syntax
sp_addrolemember [ @.rolename = ] 'role' ,
[ @.membername = ] 'security_account'
So it should be
sp_addrolemember 'db_datareader','CH-datareader-K'
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Joe K." <Joe K.@.discussions.microsoft.com> schrieb im Newsbeitrag
news:F7FFAA86-280E-45B2-93E3-F52AAB86D203@.microsoft.com...
> I have a SQL Server 2000 database that I restore often.
> After the database is restored I need to add database security role to
> Login
> Name Chicago\CH-datareader-K.
> How would I add the database role 'db_datareader' to Login Name
> Chicago\CH-datareader-K that correponds to User Name CH-datareader-K.
> Please help me create the correct syntax so I add this to my restore
> script.
> Thanks,
After the database is restored I need to add database security role to Login
Name Chicago\CH-datareader-K.
How would I add the database role 'db_datareader' to Login Name
Chicago\CH-datareader-K that correponds to User Name CH-datareader-K.
Please help me create the correct syntax so I add this to my restore script.
Thanks,Syntax
sp_addrolemember [ @.rolename = ] 'role' ,
[ @.membername = ] 'security_account'
So it should be
sp_addrolemember 'db_datareader','CH-datareader-K'
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Joe K." <Joe K.@.discussions.microsoft.com> schrieb im Newsbeitrag
news:F7FFAA86-280E-45B2-93E3-F52AAB86D203@.microsoft.com...
> I have a SQL Server 2000 database that I restore often.
> After the database is restored I need to add database security role to
> Login
> Name Chicago\CH-datareader-K.
> How would I add the database role 'db_datareader' to Login Name
> Chicago\CH-datareader-K that correponds to User Name CH-datareader-K.
> Please help me create the correct syntax so I add this to my restore
> script.
> Thanks,
Thursday, February 16, 2012
add a login to a database role (was "Very Confusing")
This stuff makes no sense what so ever,
In the Books on Line of MSSQL2000
In "Adding a Member to a SQL Server Database Role"
There is this
"Note : When you add a Windows NT 4.0 or Windows 2000 login without a user account in the database to a SQL Server database role, SQL Server creates a user account in the database automatically, even if that Windows NT 4.0 or Windows 2000 login cannot otherwise access the database."
I mean how can anyone add a login to a database role without making the login a user of the database.:shocked:
Also if it worked, a new fancy feature to add new logins??:eek:
Plz help:SI believe it adds it as a user automatically.|||I believe it adds it as a user automatically.
But i cannot do it:eek:
"user" must exist before "role".
If you can do it,plz explain:)|||The following discussion assumes that BUILTIN\Administrators is part of the SQL Server sysadmin group:
1. Create a junk Windows login
2. Make that login a member of the machine's Administrators group
3. Start SQL Enterprise Mangler
4. Select the target database
5. Create a new (junk) role within the database.
6. Verify there is no user in the db for your junk login
7. Add your junk Windows login to your junk role.
8. Verify there is now a user in the db for your junk login
The reason that you could add the login to the role was because they implicitly have access to the database because they are members of the sysadmin role. Once you assign them to a role within the database, SQL Server needs some way to store that membership information, so it has to create a user where none explicitly existed before.
-PatP|||7. Add your junk Windows login to your junk role.
-PatP
1&2>created a user account with account type computer administrator.
3>started enterprise manager
4>selected a target db
5>created junk dbrole
6>verified no junk user
7>double clicked junk dbrole, clicked add..
:confused: junk user not there:confused:
Only users who are database "users" are appearing.
I am working on an XP,maybe thats why the difference.|||So you can see the junk login inside the junk group, but you can not see the junk login in the database users ?!?!
-PatP|||No, the junk login does not appear when i click the add button of the junk role.
The junklogin will appear only if i give junklogin access to the SQL server first and then make the junklogin a user of the database first.
Only after this is done, can i see the junklogin, infact any login works this way.|||I'm one of the "scripts our us" kind of guys... Rather than asking you to do 10,000 clicks, can you run:DECLARE @.cJunque sysname
SELECT @.cJunque = 'MyMachine\JunqueLogin' -- Machine Administrator
EXECUTE sp_helpuser @.cJunque -- Prove "no one home"
EXECUTE sp_addrole 'Junque' -- Create group
EXECUTE sp_helprole 'Junque' -- Confirm still empty
EXECUTE sp_addrolemember 'Junque', @.cJunque -- Add junk login
EXECUTE sp_helprole 'Junque' -- See who's "home" now
EXECUTE sp_helpuser @.cJunque -- Should be a user
EXECUTE sp_droprole 'Junque' -- Tidy up...
EXECUTE sp_dropuser @.cJunque -- ...completely...and post the output?
-PatP|||That worked :D
It even says in Enterprise Manager, "Database Access : Via group membership" for junkuser.
Only i think the user has to be droped before the group.
I dont think this can be done through Enterprise Manager directly??
:beer: Thanks.:beer:|||Server: Msg 15198, Level 16, State 1, Procedure sp_helpuser, Line 189
The name supplied (piii\junkuser) is not a user, role, or aliased login.
New role added.
(1 row(s) affected)
'piii\dbtemp' added to role 'Junque'.
(1 row(s) affected)
Server: Msg 15198, Level 16, State 1, Procedure sp_helpuser, Line 189
The name supplied (piii\junkuser) is not a user, role, or aliased login.
Server: Msg 15144, Level 16, State 1, Procedure sp_droprole, Line 53
The role has members. It must be empty before it can be dropped.
User has been dropped from current database.
In the Books on Line of MSSQL2000
In "Adding a Member to a SQL Server Database Role"
There is this
"Note : When you add a Windows NT 4.0 or Windows 2000 login without a user account in the database to a SQL Server database role, SQL Server creates a user account in the database automatically, even if that Windows NT 4.0 or Windows 2000 login cannot otherwise access the database."
I mean how can anyone add a login to a database role without making the login a user of the database.:shocked:
Also if it worked, a new fancy feature to add new logins??:eek:
Plz help:SI believe it adds it as a user automatically.|||I believe it adds it as a user automatically.
But i cannot do it:eek:
"user" must exist before "role".
If you can do it,plz explain:)|||The following discussion assumes that BUILTIN\Administrators is part of the SQL Server sysadmin group:
1. Create a junk Windows login
2. Make that login a member of the machine's Administrators group
3. Start SQL Enterprise Mangler
4. Select the target database
5. Create a new (junk) role within the database.
6. Verify there is no user in the db for your junk login
7. Add your junk Windows login to your junk role.
8. Verify there is now a user in the db for your junk login
The reason that you could add the login to the role was because they implicitly have access to the database because they are members of the sysadmin role. Once you assign them to a role within the database, SQL Server needs some way to store that membership information, so it has to create a user where none explicitly existed before.
-PatP|||7. Add your junk Windows login to your junk role.
-PatP
1&2>created a user account with account type computer administrator.
3>started enterprise manager
4>selected a target db
5>created junk dbrole
6>verified no junk user
7>double clicked junk dbrole, clicked add..
:confused: junk user not there:confused:
Only users who are database "users" are appearing.
I am working on an XP,maybe thats why the difference.|||So you can see the junk login inside the junk group, but you can not see the junk login in the database users ?!?!
-PatP|||No, the junk login does not appear when i click the add button of the junk role.
The junklogin will appear only if i give junklogin access to the SQL server first and then make the junklogin a user of the database first.
Only after this is done, can i see the junklogin, infact any login works this way.|||I'm one of the "scripts our us" kind of guys... Rather than asking you to do 10,000 clicks, can you run:DECLARE @.cJunque sysname
SELECT @.cJunque = 'MyMachine\JunqueLogin' -- Machine Administrator
EXECUTE sp_helpuser @.cJunque -- Prove "no one home"
EXECUTE sp_addrole 'Junque' -- Create group
EXECUTE sp_helprole 'Junque' -- Confirm still empty
EXECUTE sp_addrolemember 'Junque', @.cJunque -- Add junk login
EXECUTE sp_helprole 'Junque' -- See who's "home" now
EXECUTE sp_helpuser @.cJunque -- Should be a user
EXECUTE sp_droprole 'Junque' -- Tidy up...
EXECUTE sp_dropuser @.cJunque -- ...completely...and post the output?
-PatP|||That worked :D
It even says in Enterprise Manager, "Database Access : Via group membership" for junkuser.
Only i think the user has to be droped before the group.
I dont think this can be done through Enterprise Manager directly??
:beer: Thanks.:beer:|||Server: Msg 15198, Level 16, State 1, Procedure sp_helpuser, Line 189
The name supplied (piii\junkuser) is not a user, role, or aliased login.
New role added.
(1 row(s) affected)
'piii\dbtemp' added to role 'Junque'.
(1 row(s) affected)
Server: Msg 15198, Level 16, State 1, Procedure sp_helpuser, Line 189
The name supplied (piii\junkuser) is not a user, role, or aliased login.
Server: Msg 15144, Level 16, State 1, Procedure sp_droprole, Line 53
The role has members. It must be empty before it can be dropped.
User has been dropped from current database.
Sunday, February 12, 2012
AD groups and database roles
We have AD and SQL Server 2005.
I have made grants to a database role called 'App Users'. I then set up
the AD group as a login, and tried to map to the proper database and the App
Users database role. This last step is failing with a 'principal cannot be
found' error. Is there any limitation to use AD groups in this way? Seems
that it should be pretty straightforward.
Thanks,
DavidDavid
I could not reproduce it , can you provide a script or step by step what
you did so far?
1) Crate a database Role called 'MyRole'
2) Create a new login called 'Test'
3) Add the the new user (mapped to the login) to the desired database
4) Add a database user to the Role
"David R" <DavidR@.discussions.microsoft.com> wrote in message
news:99E59589-24C0-44C1-9335-6B039E2F91FE@.microsoft.com...
> We have AD and SQL Server 2005.
> I have made grants to a database role called 'App Users'. I then set up
> the AD group as a login, and tried to map to the proper database and the
> App
> Users database role. This last step is failing with a 'principal cannot
> be
> found' error. Is there any limitation to use AD groups in this way?
> Seems
> that it should be pretty straightforward.
> Thanks,
> David|||Thanks for the response, but never mind. I tried it again, after having a
new group created, and it worked without a problem. Don't know why it faile
d
the first time.
David
"Uri Dimant" wrote:
> David
> I could not reproduce it , can you provide a script or step by step what
> you did so far?
>
> 1) Crate a database Role called 'MyRole'
> 2) Create a new login called 'Test'
> 3) Add the the new user (mapped to the login) to the desired database
> 4) Add a database user to the Role
>
>
> "David R" <DavidR@.discussions.microsoft.com> wrote in message
> news:99E59589-24C0-44C1-9335-6B039E2F91FE@.microsoft.com...
>
>
I have made grants to a database role called 'App Users'. I then set up
the AD group as a login, and tried to map to the proper database and the App
Users database role. This last step is failing with a 'principal cannot be
found' error. Is there any limitation to use AD groups in this way? Seems
that it should be pretty straightforward.
Thanks,
DavidDavid
I could not reproduce it , can you provide a script or step by step what
you did so far?
1) Crate a database Role called 'MyRole'
2) Create a new login called 'Test'
3) Add the the new user (mapped to the login) to the desired database
4) Add a database user to the Role
"David R" <DavidR@.discussions.microsoft.com> wrote in message
news:99E59589-24C0-44C1-9335-6B039E2F91FE@.microsoft.com...
> We have AD and SQL Server 2005.
> I have made grants to a database role called 'App Users'. I then set up
> the AD group as a login, and tried to map to the proper database and the
> App
> Users database role. This last step is failing with a 'principal cannot
> be
> found' error. Is there any limitation to use AD groups in this way?
> Seems
> that it should be pretty straightforward.
> Thanks,
> David|||Thanks for the response, but never mind. I tried it again, after having a
new group created, and it worked without a problem. Don't know why it faile
d
the first time.
David
"Uri Dimant" wrote:
> David
> I could not reproduce it , can you provide a script or step by step what
> you did so far?
>
> 1) Crate a database Role called 'MyRole'
> 2) Create a new login called 'Test'
> 3) Add the the new user (mapped to the login) to the desired database
> 4) Add a database user to the Role
>
>
> "David R" <DavidR@.discussions.microsoft.com> wrote in message
> news:99E59589-24C0-44C1-9335-6B039E2F91FE@.microsoft.com...
>
>
Subscribe to:
Posts (Atom)