Post reply

Programtically Check Permissions

  • April 5, 2006 at 7:34 am

    #96930

    Been programming code for SQL Server based applications for years and have never come up with a good solution for this issue. I'm sure I've missed something and I hope one of you can help.

    Here is the deal. I'm using Windows Authentication. I have created local groups on the SQL Server that have domain groups and windows logins as memebers. I then create SQL logins based on those local groups and give that login access to my DB. I also create DB roles which is where all my permissions are granted. The users are members of this role. The Local Groups on the SQL Server are role centric in that if I plan on having a read only role in database xyz I create an xyz_readers local group on the SQL Server. The SQL login and DB User end up with the same name. I will also have somthing like xyz_writers.

    Anyway I then use integrated security in the connection string in my code. What happens is that the user_name() function reports my domain\login as my username. All the permissions get worked out just fine, but I have no way of determining via script what I have access to. Sp_helprolemember and sp_helprotect look promising, but my AD login doesn't show up there. All that shows up is xyz_readers...

    Anyone have a way to programtically check permissions for integrated logins? I would like the application to make the check and change the UI accordingly. For example if the login can read only then the update/create buttons should be disabled.

    Any help would be great!


    Adam Goss
    DBA

  • April 5, 2006 at 7:46 am

    #630998

    Have you looked at IS_MEMBER ( { 'group' | 'role' } )

    Tim S

  • April 5, 2006 at 8:23 am

    #631011

    Ahhh... it seems that most of my testing was done with my account which is a member of the Local Admins group on the server. I get mapped to dbo and is_member doesn't report that I am a member of the user defined, xyz role, but I am a member of the db_owner role.

    I've created a test user as a local user and run QA as that user. Looks like it reports correctly. I guess I'll need to run sp_helprotect to get a list of roles with the desired permission, loop through that list and see if the account is a memeber of one of those roles. I'll need to check for db_owner membership in addition since it doesn't get reported via sp_helprotect.

    Thanks for the help. I guess another "feature" was hindering my testing. I'm glad I don't have to duplicate a permissioning scheme in my application anymore!


    Adam Goss
    DBA

Viewing 3 posts - 1 through 2 (of 2 total)

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