locked
Limited access user cannot run application RRS feed

  • Question

  • Hi all,

    I have released my application that has database files that will be copied to the user's computer when the admin installs the program. The user's computer's OS is in German, not English. On that computer, the Admin runs the program well, but the limited access user cannot run the program with error message: "CREATE DATABASE permission failed in database 'Master'...". However, I have tested on two of my computers with English OS, the program works fine with limited access users. I have set permissions for all guests in database files. SQL server allows all users to login. I don't know why that happens? Could anyone please help me? Thanks in advance.

    Watson  

     

     


    pss
    • Edited by watson pg Wednesday, December 21, 2011 7:54 PM
    Wednesday, December 21, 2011 7:53 PM

Answers

  • Sorry for my late reply.

    I just got the test result today. The reason is that the language issue. Here is some codes how to do it:

    string ConnStrDB = @"Data Source=(local)\SQLEXPRESS;AttachDbFilename= myDatabasefile.mdf;Integrated Security=True;";//connect to databasefile.mdf

    System.Security.Principal.SecurityIdentifier sid = new System.Security.Principal.SecurityIdentifier(System.Security.Principal.WellKnownSidType.BuiltinGuestsSid, null);

    System.Security.Principal.NTAccount acct = sid.Translate(typeof(System.Security.Principal.NTAccount)) as System.Security.Principal.NTAccount;

    string strBuiltinGuest = acct.ToString();

    string str = "GRANT ALTER,BACKUP DATABASE,BACKUP LOG,CONNECT,CREATE FUNCTION,CREATE PROCEDURE,CREATE SCHEMA,CREATE TABLE,CREATE VIEW,DELETE,EXECUTE,INSERT,REFERENCES,SELECT,UPDATE TO [" + strBuiltinGuest + "]";

    UpdateTables(ConnStrDB, str);//a method to execute sql query command

    Thanks

    Watson

     

     


    pss
    • Marked as answer by watson pg Tuesday, January 31, 2012 2:04 AM
    Tuesday, January 31, 2012 2:03 AM

All replies

  • Grant the use create any database permissions -

     

    GRANT CREATE ANY DATABASE TO USER


    http://uk.linkedin.com/in/ramjaddu
    Wednesday, December 21, 2011 8:27 PM
  • As RamJeddu says, you need to grant permission to a user to permit him to createa a database. User does not have this permission by default, neither on English nor German operating systems.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, December 21, 2011 10:43 PM
  • Hi RamJaddu,

    Thank you for your reply.

    I have already granted the create permission to builtin user: "GRANT CREATE ANY DATABASE TO [BUILTIN\\Users]"

    Thanks


    pss
    Thursday, December 22, 2011 4:16 PM
  • Hi Erland Sommarskog,

    Thank you for your response. As I said above, I have already granted the permission for Builtin\Users. I think any limited access users should be User group. They should have the same permission as the User group. I have tested in English OS computer with Windows XP, it works fine. But it has problem on German OS computer.

    Thanks a lot


    pss
    Thursday, December 22, 2011 4:22 PM
  • Hi all,

    By the way, later on, I thought that it was maybe caused by the access limitations of the database files with Windows authentication. So, I set the [Builtin\Users] have full control priviledge to database files. But it still doens't work.

    Thanks


    pss
    Thursday, December 22, 2011 4:30 PM
  • Can you post the output from this query on the problematic server:

    SELECT sp.name, sp.type_desc, perm.class_desc, perm.permission_name, perm.state_desc
    FROM     sys.server_permissions perm
    JOIN     sys.server_principals sp ON sp.principal_id = perm.grantee_principal_id

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, December 22, 2011 10:49 PM
  • Hi Erland Sommarskog,

    Thank you. Here is the test result.

    I have compaired it with the result of English OS. The only difference is the language, like the "VORDEFINIERT\Benutzer" means "Builtin\Users". In my code I used "Builtin\Users" to grant permission. I think that the problem is the language issue. So, now, I manage to automatically translate it into the same language as the user's OS to grant the permission to the "Builtin\Users". The test result will be posted after I get the result. 

    Wish everyone merry Christmas and happy new year!

    Watson


    pss

    • Edited by watson pg Friday, December 23, 2011 10:50 PM
    Friday, December 23, 2011 6:01 PM
  • I suspected that it was a language issue, but since you said that you had granted BUILTIN\Users permission, I assumed that would have noticed that it gave an error. Now that you say that you do it from code, that makes me wonder if you have proper error handling, or if you drop errors on the floor.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, December 23, 2011 9:57 PM
  • Hi Erland Sommarskog,

    Yes. I forgot to tell you that the limited access user ran the program, it throwed an error:

    As to handling the error message when the program sets user permission, if there is error,it should be caught and shown in a message box. all exceptions will be caught.

    Thanks   


    pss

    • Edited by watson pg Friday, December 23, 2011 10:45 PM
    Friday, December 23, 2011 10:35 PM
  • Yes. I forgot to tell you that the limited access user ran the program, it throwed an error:<http://social.microsoft.com/Forums/getfile/47219/>

    That you told us about.

    As to handling the error message when the program sets user permission, if there is error,it should be caught and shown in a message box. all exceptions will be caught.

    Well, apparently you failed to notice, because you told us that you had set permissions. Which you after all hadn't.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Peja Tao Thursday, December 29, 2011 6:09 AM
    • Marked as answer by Peja Tao Friday, December 30, 2011 1:06 AM
    • Unmarked as answer by watson pg Sunday, January 1, 2012 3:05 AM
    Saturday, December 24, 2011 12:47 PM
  • Hi, Erland Sommarskog

    Sorry for the late reply.

    Acturally, the user in Germany had tested the program with two database file cases. One case is that database file had not been set user permission, the other case is that the database file had been pre-setting for all users. Both cases had the above error.

    Thanks a lot

    Happy new year

    Watson

     

     


    pss
    Sunday, January 1, 2012 3:16 AM
  • My point is that none of the database had the pre-setting - since "BUILTIN\Users" does not exist. The difference is that for one of the database, you thoght you had the setting in place. But the error message when you or your program tried to set the permissions was apparently dropped on the floor. Because if anyone had paid attention, you would never had reason to start this thread.

    And I think there is all reason to investigate why this error was not noticed. At least you should make sure that when your installation tries to assign permission that it does hide the error.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, January 1, 2012 10:12 AM
  • Sorry for my late reply.

    I just got the test result today. The reason is that the language issue. Here is some codes how to do it:

    string ConnStrDB = @"Data Source=(local)\SQLEXPRESS;AttachDbFilename= myDatabasefile.mdf;Integrated Security=True;";//connect to databasefile.mdf

    System.Security.Principal.SecurityIdentifier sid = new System.Security.Principal.SecurityIdentifier(System.Security.Principal.WellKnownSidType.BuiltinGuestsSid, null);

    System.Security.Principal.NTAccount acct = sid.Translate(typeof(System.Security.Principal.NTAccount)) as System.Security.Principal.NTAccount;

    string strBuiltinGuest = acct.ToString();

    string str = "GRANT ALTER,BACKUP DATABASE,BACKUP LOG,CONNECT,CREATE FUNCTION,CREATE PROCEDURE,CREATE SCHEMA,CREATE TABLE,CREATE VIEW,DELETE,EXECUTE,INSERT,REFERENCES,SELECT,UPDATE TO [" + strBuiltinGuest + "]";

    UpdateTables(ConnStrDB, str);//a method to execute sql query command

    Thanks

    Watson

     

     


    pss
    • Marked as answer by watson pg Tuesday, January 31, 2012 2:04 AM
    Tuesday, January 31, 2012 2:03 AM