Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2006
    Posts
    1

    Unanswered: SQL Server 2005 Security - TSQL

    Hi All,

    I have been building a database in SQL Server Express for some months now using the Windows level authentication login that has given me full access to everything in the database.

    The time has now come where I need to create user accounts and grant permissions to specific stored procedures and I'm having trouble doing this.

    Can anyone give me a brief rundown of the required T-SQL commands I need to set up a user account that can do nothing but run stored procs (not the system procs which apparently are being discontinued: http://msdn2.microsoft.com/en-us/library/ms182795.aspx).

    So far I have:-

    CREATE LOGIN db_test_user WITH PASSWORD = 'eXaMpL3Pwd
    USE db_new
    CREATE USER db_test_user

    The above code executes successfully but when I try and connect (using Management Studio) I get error message 'The user is not associated with a trusted SQL Server connection (error 18452)' which means little to me.

    I also tried creating a 'WITHOUT LOGIN' user for the database but could not figure out how to give it a password.

    I don't know (/understand) roles/schemas and don't know if I really need them as I only need user access to specific stored procs. I don't ever want them to see the tables for example so they only need the most restricted access.

    Can anyone help?

    Thanks,

    DG

  2. #2
    Join Date
    Jul 2002
    Posts
    229
    I guess you'll need to do an sp_addlogin , an sp_grantdbaccess , an sp_addrolemember. Then a Grant Execute on the SPs.

  3. #3
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up

    Check this example.I tested in SQL 2005
    Code:
    if not exists(select null from sys.server_principals where name='test')
    begin
               CREATE LOGIN [test] WITH PASSWORD=N'test123'
               use AdventureWorks
               CREATE USER [test] FOR LOGIN [test]
               GRANT ALTER ON [dbo].[ur_SP_name] TO [test]
               GRANT EXECUTE ON [dbo].[ur_SP_name] TO [test]
    end
    
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •