Check if Windows User is an SQL Admin

Question

Check if Windows User is an SQL Admin

JP-1019544

SSC-Addicted

Points: 409
More actions
February 17, 2009 at 11:25 pm

#197286

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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 1

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

JP-1019544 SSC-Addicted Points: 409 More actions · Answer 2

Thanks

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

JP

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 3

http://msdn.microsoft.com/en-us/library/ms186271(SQL.90).aspx

blasto_max SSCertifiable Points: 5025 More actions · Answer 4

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 -

EdVassie SSC Guru Points: 60445 More actions · Answer 5

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: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 6

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

EdVassie SSC Guru Points: 60445 More actions · Answer 7

The application should be calling on prived stored procedures to do the job safely.

+1

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara