Running SQL Server 2000 under Windows 2003 server. All
updates and service packs applied. Using Windows
Authentication via group membership. No individual users
are defined in SQL Server. Some users can update tables,
while other users in the same group cannot (permission
denied).
I'm baffled...These people are probably members of another group that has been denied
those rights. Remember that permissions are cumulative except that deny
trumps...
"Jay Varner" <dimsjay@.dims-vote.com> wrote in message
news:e55901c3f0e8$da00f2c0$a301280a@.phx.gbl...
> Running SQL Server 2000 under Windows 2003 server. All
> updates and service packs applied. Using Windows
> Authentication via group membership. No individual users
> are defined in SQL Server. Some users can update tables,
> while other users in the same group cannot (permission
> denied).
> I'm baffled...|||Try comparing users that can update vs those that can't using gpresult.
321709 HOW TO: Use the Group Policy Results Tool in Windows 2000
http://support.microsoft.com/?id=321709
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||I appreciate your quick reply, Don, but am not sure I expressed the
problem sufficiently.
Using Active Directory in W2K3 server, we defined specific user groups
for each of three domains that need to access the database. Each user
appears in only one group. All of these groups have been added to SQL
Server, and all have the same rights to the database(s). The problem
seems to occur for several people in each group, and does not seem to be
related to any permissions they are allowed on the domain. For
instance, in one of the groups, one user is a standard DOMAIN USER in
Windows, and he is able to perform updates to any of the tables in the
database; another user is a DOMAIN ADMIN who belongs to the same group,
but he is denied access to perform updates.
If we assign the group SYSTEM ADMINISTRATOR priveleges on the database,
it seems to resolve the problem, but thats not an acceptable resolution
for this large office.
If we assign each individual user (rather than the groups) to SQL, the
problem goes away. Again, this is a large office, and they would like
to avoid the additional overhead of having to add each new user to both
Windows and SQL Server.
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!
Showing posts with label membership. Show all posts
Showing posts with label membership. Show all posts
Monday, March 26, 2012
Permissions via ASP.NET web site
I have a website with membership system. I've been through a lot implementing the site and I think I'm on the verge of full implementation. I can't get my registration page or my admin page to add, edit or delete users. I'm assuming that asp does not have proper permissions on SQL to do such.
I'm running the site on a provider that has a SQL 2000 server. I access the database through VS2005 and SQL Server Management Studio Express.
Is there a tutorial out there that could help a newbie to SQL understand how to check and correct my permissions issue (or find out that I'm haveing a different issue entirely.
-Bill
SQL Server 2000 require you to go in and manually create the account before you can run it I have answered this question in the thread below go through it and apply the instructions in the last link and post again if you still need help. Hope this helps.
http://forums.asp.net/thread/1534299.aspx
|||Thank you for your prompt reply.
Further research indicates that my roles are conflicted also. My registrations are being created, but I can't see the role that's being created for them. Once I figure that out, I'll be able to add that role to the proper table and be able to see all of the users I create. Editing and deleting users is still not possible given the instructions you posted. I still need to change the permissions from my admin page. The create user is working.
Thank you
|||You are not supposed to edit and delete users so delete the database you have created and start new this time do everything in SQL Server 2000 because Express and 2000 are not the same. Hope this helps.
http://blog.vanslaars.com/archive/2006/05/17/ASP.NET-2.0-Membership-with-SQL-2000.aspx
sqlTuesday, March 20, 2012
Permissions in SQL Server 2005 to allow users to view the Management Activity monitor?
For SQL Server 2000 we have a user login mapped to msdb with database role membership of db_datareader and public checked. This seems to allow the developers to view the Management Activity monitor. For SQL Server 2005 the same mapping is in place but the developers cannot view the Management Activity monitor. Developers are NOT granted the sysadmin role, and should not have that role.
What permissions need to be set for SQL Server 2005 to allow users to view the Management Activity monitor? They should not be allowed to take actions on the activities.
The Active Monitor tool requires VIEW SERVER STATE permission; this permission is required to select from DMVs such as sys.sysprocesses.
-Raul Garcia
SDE/T
SQL Server Engine
|||Thank you so much! This did the trick.
Barb
|||Does Management Studio Express support the Activity Monitor?
I've enabled VIEW SERVER STATE for admin and still can't find the Activity Monitor in the GUI.
Thanks
|||Unfortunately I am not familiar with the different GUI tools available for SQL Express. I would recommend asking this question on the SQL Express forum (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=385&SiteID=1) instead.
Thanks,
-Raul Garcia
SDE/T
SQL Server Engine
|||Where do you set this: To view the Activity Monitor the user needs VIEW SERVER STATE permission on a SQL Server 2005 server.
|||The easiest way is to just open a connection (i.e. using sqlcmd or Management Studio) using your sysadmin credentials and issue the following statement:
GRANTVIEWSERVER STATETO <<login name>>
This will grant <<login_name>> VIEW ANY STATE permission.
-Raul Garcia
SDE/T
SQL Server Engine
Permissions in SQL Server 2005 to allow users to view the Management Activity monitor?
For SQL Server 2000 we have a user login mapped to msdb with database role membership of db_datareader and public checked. This seems to allow the developers to view the Management Activity monitor. For SQL Server 2005 the same mapping is in place but the developers cannot view the Management Activity monitor. Developers are NOT granted the sysadmin role, and should not have that role.
What permissions need to be set for SQL Server 2005 to allow users to view the Management Activity monitor? They should not be allowed to take actions on the activities.
The Active Monitor tool requires VIEW SERVER STATE permission; this permission is required to select from DMVs such as sys.sysprocesses.
-Raul Garcia
SDE/T
SQL Server Engine
|||Thank you so much! This did the trick.
Barb
|||Does Management Studio Express support the Activity Monitor?
I've enabled VIEW SERVER STATE for admin and still can't find the Activity Monitor in the GUI.
Thanks
|||Unfortunately I am not familiar with the different GUI tools available for SQL Express. I would recommend asking this question on the SQL Express forum (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=385&SiteID=1) instead.
Thanks,
-Raul Garcia
SDE/T
SQL Server Engine
|||Where do you set this: To view the Activity Monitor the user needs VIEW SERVER STATE permission on a SQL Server 2005 server.
|||The easiest way is to just open a connection (i.e. using sqlcmd or Management Studio) using your sysadmin credentials and issue the following statement:
GRANT VIEW SERVER STATE TO <<login name>>
This will grant <<login_name>> VIEW ANY STATE permission.
-Raul Garcia
SDE/T
SQL Server Engine
Permissions in SQL Server 2005 to allow users to view the Management Activity monitor?
For SQL Server 2000 we have a user login mapped to msdb with database role membership of db_datareader and public checked. This seems to allow the developers to view the Management Activity monitor. For SQL Server 2005 the same mapping is in place but the developers cannot view the Management Activity monitor. Developers are NOT granted the sysadmin role, and should not have that role.
What permissions need to be set for SQL Server 2005 to allow users to view the Management Activity monitor? They should not be allowed to take actions on the activities.
The Active Monitor tool requires VIEW SERVER STATE permission; this permission is required to select from DMVs such as sys.sysprocesses.
-Raul Garcia
SDE/T
SQL Server Engine
|||Thank you so much! This did the trick.
Barb
|||Does Management Studio Express support the Activity Monitor?
I've enabled VIEW SERVER STATE for admin and still can't find the Activity Monitor in the GUI.
Thanks
|||Unfortunately I am not familiar with the different GUI tools available for SQL Express. I would recommend asking this question on the SQL Express forum (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=385&SiteID=1) instead.
Thanks,
-Raul Garcia
SDE/T
SQL Server Engine
|||Where do you set this: To view the Activity Monitor the user needs VIEW SERVER STATE permission on a SQL Server 2005 server.
|||The easiest way is to just open a connection (i.e. using sqlcmd or Management Studio) using your sysadmin credentials and issue the following statement:
GRANT VIEW SERVER STATE TO <<login name>>
This will grant <<login_name>> VIEW ANY STATE permission.
-Raul Garcia
SDE/T
SQL Server Engine
Wednesday, March 7, 2012
Permission to run master..xp_cmdshell
Hi!
I have a user (SQL login), who has access to master database and the
right to run xp_cmdshell via database role membership. When running
it:
SETUSER 'Smith'
go
exec master..xp_cmdshell 'dir c:'
go
SETUSER
=============================== We get:
Msg 50001, Level 1, State 50001
xpsql.c: Error 997 from GetPassword on line 465
When I give the user the sa server role, it works fine. I am not very
happy to give so many users the sa role. Please help me to figure out,
how to allow a user to run xp_cmdshell.
/* I suspect that this is the consequences? of a recent security patch
installation (SQL2000-KB815495-8.00.0818-ENU.exe), since there were no
complains about it before */
Thanks.I assume you are running SQL 7. This error may indicate a problem with
the proxy and/or service accounts.
To ensure the proxy account is configured properly, use Enterprise
Manager to navigate to Management --> SQL Server Agent --> Job System
and ensure 'only allow users with Sysadmin privileges ...' is unchecked
and click the Reset Proxy Account button.
If the problem persists, ensure the SQL Server service account has the
needed permissions. The easiest way to do this is to re-specify the
service account using Enterprise Manager (server properties -->
Security). Assuming you are using a domain account, change it to the
System account and then back to the domain account. Enterprise Manager
will assign the domain account the necessary rights during the change.
You'll need to restart SQL Server after the change. Sometimes a reboot
is required as well.
Separately, you might consider leveraging cross database ownership
chaining so that you don't need to grant direct execute permissions to
users on xp_cmdshell. If your proc is owned by dbo, all you need to do
is change ownership of your user database to the 'sa' login. Due to the
unbroken ownership chain, users can then execute xp_cmdshell only via
your user proc and cannot execute it directly. However, you should
employ this technique only if you fully trust users that have
permissions to create dbo-owned objects in your user database.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Roust_m" <roustam@.hotbox.ru> wrote in message
news:a388fd78.0308192241.7a70bf5f@.posting.google.com...
> Hi!
> I have a user (SQL login), who has access to master database and the
> right to run xp_cmdshell via database role membership. When running
> it:
> SETUSER 'Smith'
> go
> exec master..xp_cmdshell 'dir c:'
> go
> SETUSER
> ===============================> We get:
> Msg 50001, Level 1, State 50001
> xpsql.c: Error 997 from GetPassword on line 465
> When I give the user the sa server role, it works fine. I am not very
> happy to give so many users the sa role. Please help me to figure out,
> how to allow a user to run xp_cmdshell.
> /* I suspect that this is the consequences? of a recent security patch
> installation (SQL2000-KB815495-8.00.0818-ENU.exe), since there were no
> complains about it before */
> Thanks.
I have a user (SQL login), who has access to master database and the
right to run xp_cmdshell via database role membership. When running
it:
SETUSER 'Smith'
go
exec master..xp_cmdshell 'dir c:'
go
SETUSER
=============================== We get:
Msg 50001, Level 1, State 50001
xpsql.c: Error 997 from GetPassword on line 465
When I give the user the sa server role, it works fine. I am not very
happy to give so many users the sa role. Please help me to figure out,
how to allow a user to run xp_cmdshell.
/* I suspect that this is the consequences? of a recent security patch
installation (SQL2000-KB815495-8.00.0818-ENU.exe), since there were no
complains about it before */
Thanks.I assume you are running SQL 7. This error may indicate a problem with
the proxy and/or service accounts.
To ensure the proxy account is configured properly, use Enterprise
Manager to navigate to Management --> SQL Server Agent --> Job System
and ensure 'only allow users with Sysadmin privileges ...' is unchecked
and click the Reset Proxy Account button.
If the problem persists, ensure the SQL Server service account has the
needed permissions. The easiest way to do this is to re-specify the
service account using Enterprise Manager (server properties -->
Security). Assuming you are using a domain account, change it to the
System account and then back to the domain account. Enterprise Manager
will assign the domain account the necessary rights during the change.
You'll need to restart SQL Server after the change. Sometimes a reboot
is required as well.
Separately, you might consider leveraging cross database ownership
chaining so that you don't need to grant direct execute permissions to
users on xp_cmdshell. If your proc is owned by dbo, all you need to do
is change ownership of your user database to the 'sa' login. Due to the
unbroken ownership chain, users can then execute xp_cmdshell only via
your user proc and cannot execute it directly. However, you should
employ this technique only if you fully trust users that have
permissions to create dbo-owned objects in your user database.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Roust_m" <roustam@.hotbox.ru> wrote in message
news:a388fd78.0308192241.7a70bf5f@.posting.google.com...
> Hi!
> I have a user (SQL login), who has access to master database and the
> right to run xp_cmdshell via database role membership. When running
> it:
> SETUSER 'Smith'
> go
> exec master..xp_cmdshell 'dir c:'
> go
> SETUSER
> ===============================> We get:
> Msg 50001, Level 1, State 50001
> xpsql.c: Error 997 from GetPassword on line 465
> When I give the user the sa server role, it works fine. I am not very
> happy to give so many users the sa role. Please help me to figure out,
> how to allow a user to run xp_cmdshell.
> /* I suspect that this is the consequences? of a recent security patch
> installation (SQL2000-KB815495-8.00.0818-ENU.exe), since there were no
> complains about it before */
> Thanks.
Subscribe to:
Posts (Atom)