locked
Application login RRS feed

  • Question

  • Hi Every Body,

    I need to create a application login it should be used by only applications , it should have alter , delete , update, exicute, create , select , insert  permissions on each of the objects in the server,

    and db creation permission also, iam using sql server 2005

    can any body please  help me to fulfill the above requirement?

     

     

    Tuesday, July 19, 2011 5:03 PM

Answers

  • First of all, I did not mention application roles, and I don't think application roles meet your needs very well. An application role is a database entity, so you would need to create an application role in each database. If the application can move between databases in the same execution, you would need to unset and set the application role each time you change. Furthermore, since application roles are database-level entities, you cannot grant CREATE DATABASE to application role.

    So the option that remains to you is to create a standard login:

    CREATE LOGIN myapplogin WITH PASSWORD='vERrY d1ffi¢uLtt'
    EXEC sp_addsrvrolemember 'myapplogin', 'dbcreator'

    Note: I may have swapped the arguments to sp_addsrvrolemember. Please check Books Online for the exact parameter order.

    For existing databases, I suggest that you make this login the owner of those databases:

    ALTER AUTHORIZATION ON DATABASE::thatolddatabaseinthecorner TO myapplogin

    If you don't want this login to be the owner of the databases, albeit it creates them, you will need to grant this login all permissions needed per databases. You cannot grant the permissions you listed on server-level.

    Again, I like to repeat the warning, that this solution is secure if there is a middle-tier application where only authorised staff has access to the middle tier. If you embed the password in a plain two-tier Windows applications, the users will be able to find the password.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, July 20, 2011 9:57 PM
  • What you do is to create a certificate, and create a user from the certifcate and grant that user the necessary permissions. Then you sign the procedure with the certificate. Now the procedure will bring the necessary permissions.

    For a step-by-step description, see this article on my web site: http://www.sommarskog.se/grantperm.html


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, July 24, 2011 3:44 PM

All replies

  •  

    You can Create SQL Authetication Account with Password and give sysadmin Role this will gives complete access to the server.


    Nag Pal MCTS/MCITP (SQL Server 2005/2008)
    Tuesday, July 19, 2011 6:16 PM
  • What is the architecture of the application? Is this ia a three-tier application then this can be a workable path, because in this case you can store the password for this login, so that the users cannot access it.

    Nag Pal suggested that you would make this login sysadmin, which is bad advice. Never hand more permissions than requires. You need to add this login to the fixed server role dbcreator, so that it can create databases. Once it has created a database, it owns that database and has full permissions in that database.

    For existing databases you can say:

    GRANT ALTER TO appllogin
    GRANT DELETE TO appllogin

    etc,


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, July 19, 2011 10:02 PM
  • Hi Nag Pal

     

    Thanks for your quick response, I don't want to give more permissions than required,can you suggest another way?

    Wednesday, July 20, 2011 4:15 AM
  • Hi Erland ,

    Can you expline me more about this?

    thanks

     

     

    Wednesday, July 20, 2011 4:19 AM
  • > Can you expline me more about this?

    More about what? My initial caveat or the second part?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, July 20, 2011 8:12 AM
  • How to create Application login?need we create application role for all the dbs  ? etc

    can you please give me the above info

    Wednesday, July 20, 2011 10:15 AM
  • I Agree with Erland Sommarskog sugession. Add login to the dbCreator server role and that should work in this scenario.
    Swami A Polnati.
    Wednesday, July 20, 2011 10:18 AM
  • MSK38653:

    I don't know your business need here, so I might be completely off topic here, but I think I should suggest a different approach. You appear to be putting all your business logic into your application and you want to give that application a lot of control. I think you should consider moving a substantial amount of the business logic to the database. For example, the application should never have permission to insert data into a table. You should create a stored procedure in the database, that accepts data parameters for the insert, validates the appropriatness of the data, and then the procedure does the insert into the table. The application or the user running the application is granted the privilege to execute the procedure, but never gets any permission on the table. When you are done creating such a system, the database will have a bunch of stored procedures. The application will have the permission to execute them, but won't be able to do anything else. Users connecting directly to the database, might (if you wish) be able to execute the procedures if they submit valid data changes, but they can't do anything else either. This approach will make your application faster by caching stored procedure plans and reducing network traffic, and it will make it harder for a malicious user to access your data through SQL Injection attacks.

    I know this isn't really addressing the question you asked, so I hope I don't offend by offering this advice.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    Wednesday, July 20, 2011 2:45 PM
  • Hi,

    Creating App Role is depends on business requirement but i will try to suggest you alternatives if they are meeting your requirements you can falllow

    you can create a custom database role as per requirement and provide access for app login instead of sysadmin

    Because app role can be use only if your applications is capable to handle it

    In SQL Server 2001  Custom server roles introduced and will give you straight solution .  but in earlier versions we have to dependent on custom database roles

    1.create custom database role in all user databases

    2.Grant required privileges for that Role

    3.Create  login and provide database access as member of newly created role

    4.make sure that this login should be used my application

    Hope you got idea

    If you are having number of databases you can use script to achieve this

    EXEC master..sp_MSForeachdb
    'USE [?]
    IF DB_ID(''?'') > 4
    CREATE ROLE db_custom2 AUTHORIZATION [dbo] '  --Creates custom role

     

    GRANT SELECT,INSERT,UPDATE,DELETE,EXECUTE TO db_cuStoM2 --place this command in above loop to grant access

    This will provide access to all objects in dbo schema ,change it as per your requirement

    Remaining steps are not require explanation i guess :)

    caution: verify twice because sp_msforeachdb may skip few databases and undocumented as well

     

     

     

     


    Sivaprasad.L Together We can Achieve
    • Proposed as answer by TSRam Wednesday, August 17, 2011 11:10 AM
    Wednesday, July 20, 2011 5:54 PM
  • First of all, I did not mention application roles, and I don't think application roles meet your needs very well. An application role is a database entity, so you would need to create an application role in each database. If the application can move between databases in the same execution, you would need to unset and set the application role each time you change. Furthermore, since application roles are database-level entities, you cannot grant CREATE DATABASE to application role.

    So the option that remains to you is to create a standard login:

    CREATE LOGIN myapplogin WITH PASSWORD='vERrY d1ffi¢uLtt'
    EXEC sp_addsrvrolemember 'myapplogin', 'dbcreator'

    Note: I may have swapped the arguments to sp_addsrvrolemember. Please check Books Online for the exact parameter order.

    For existing databases, I suggest that you make this login the owner of those databases:

    ALTER AUTHORIZATION ON DATABASE::thatolddatabaseinthecorner TO myapplogin

    If you don't want this login to be the owner of the databases, albeit it creates them, you will need to grant this login all permissions needed per databases. You cannot grant the permissions you listed on server-level.

    Again, I like to repeat the warning, that this solution is secure if there is a middle-tier application where only authorised staff has access to the middle tier. If you embed the password in a plain two-tier Windows applications, the users will be able to find the password.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, July 20, 2011 9:57 PM
  • Hi siva,

    As you specified i tried your suggestion i implimented one DBRole to all the databases with alter,insert,select,create,exicute and update  privilages ,when i am using application i got problem with two SPs on one perticular database  in those one SP   Drops existing table and creats the table and second sp truncats the existing table and inserts new data, I dont have drop and tuncate permissions on tables now i how can i give these permissions can u please suggest me?

    Sunday, July 24, 2011 2:18 PM
  • Hi MSK,

    So ,You are facing permission problem to drop a table,truncate a different table.

    to truncate a table you need alter(at least) permission on that table.or user must be member of db_ownrer or db_ddladmin.

    you are truncating a table using stored procedure ,then try "execute as" clause to avoid giving alter explicit privilege on that table.

    The above solution is suitable for truncate and drop both.

    But i will give you advise form my experience that dont provide alter privilege (as per BOL it is a least privilege) 

    provide control permission on those table .(BOL does not mentioned ,even though it is suitable for your situation)

    Please come back if you need any clarification.

     


    Sivaprasad.L Together We can Achieve
    • Proposed as answer by TSRam Wednesday, August 17, 2011 11:10 AM
    Sunday, July 24, 2011 2:55 PM
  • What you do is to create a certificate, and create a user from the certifcate and grant that user the necessary permissions. Then you sign the procedure with the certificate. Now the procedure will bring the necessary permissions.

    For a step-by-step description, see this article on my web site: http://www.sommarskog.se/grantperm.html


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, July 24, 2011 3:44 PM
  • Thanks Siva ,

    I Ran success fully  one of my application by giving the Control permission on particular tables , Now i ran my other application  its not able to perform select * from sys.dm_tran_locks View , can You suggest me how can i grant this permission to my login

     

     

    Monday, July 25, 2011 3:23 PM
  • Hi,

    sys.dm_tran_locks View can be used for finding locking information in SQL Server.You have to provide view server state privilege.

    It is introduced in 2005.

    GRANT VIEW SERVER STATE TO [login name]

    This will provide access to the specific login, for meta-data of the server (All server level DMVs read access)


     


    Sivaprasad.L Together We can Achieve
    • Proposed as answer by TSRam Wednesday, August 17, 2011 11:10 AM
    Monday, July 25, 2011 4:04 PM
  • As Siva says, you need VIEW SERGVER STATE.

    But rather than asking here you can look the command or DMV in question in Books Online and look at the Permissions section.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, July 25, 2011 9:59 PM