Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Check if Windows User is an SQL Admin Expand / Collapse
Author
Message
Posted Tuesday, February 17, 2009 11:25 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 9, 2014 7:13 AM
Points: 11, Visits: 27
I'm building an application where it is asking the user for SQL Credentials to make Schema Updates. The credentials can be either and SQL User & Password or Trusted Connection (ie the currently logged on windows user).

Now, if the user uses Trusted Connection, the application created a connection string and connects. But how can it be sure that the user is an SQL Admin before the Schema Update starts?

Thanks

JP
Post #659094
Posted Wednesday, February 18, 2009 8:40 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, October 16, 2014 5:52 AM
Points: 2,551, Visits: 2,594
IS_MEMBER function checks whether the current user is a member of the specified windows or sql database role. And to check specifically for object level permissions, see PERMISSIONS function.

--Ramesh

Post #659493
Posted Wednesday, February 18, 2009 9:02 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 9, 2014 7:13 AM
Points: 11, Visits: 27
Thanks

Although it's not exactly what I needed, I found the Function Is_SrvRoleMember('SysAdmin') from it's help page.

JP
Post #659525
Posted Wednesday, February 18, 2009 9:09 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 12:34 PM
Points: 31,204, Visits: 15,649
http://msdn.microsoft.com/en-us/library/ms186271(SQL.90).aspx






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #659534
Posted Thursday, January 16, 2014 12:08 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 15, 2014 8:51 PM
Points: 183, Visits: 480
JP-1019544 (2/18/2009)
Thanks

Although it's not exactly what I needed, I found the Function Is_SrvRoleMember('SysAdmin') from it's help page.

JP


Your answer might not be correct as mentioned here - [url=http://stackoverflow.com/questions/4402286/how-to-check-if-user-has-system-admin-privileges-in-sql-server-2008-r2][/url]
Post #1531757
Posted Monday, January 20, 2014 6:21 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 3:41 AM
Points: 2,888, Visits: 3,261
Testing for sysadmin is not really the right thing to do in this case. Best practice is to use the lowest possible permissions needed to complete a task.

The permission needed is ALTER ANY CREDENTIAL and you should test for this. It is possible to grant this permission to any user, so you do not need to be a Sysadmin in order to create a credential.


Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2014, 2012, 2008 R2, 2008 and 2005. 18 October 2014: now over 31,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #1532596
Posted Monday, January 20, 2014 4:18 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:02 PM
Points: 35,397, Visits: 31,955
My feeling is that if an application has any more than (at the most) read/write privs, then the application is incorrectly written. The application should be calling on prived stored procedures to do the job safely.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1532812
Posted Tuesday, January 21, 2014 2:46 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 3:41 AM
Points: 2,888, Visits: 3,261
The application should be calling on prived stored procedures to do the job safely.


+1


Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2014, 2012, 2008 R2, 2008 and 2005. 18 October 2014: now over 31,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #1532924
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse