Monday, March 12, 2012

How to create a login/user & grant rights?

I'm trying to create new login/user account in C#. I'm pretty close to getting it working -- login/user accounts get created, but I'm getting hung up on granting the permissions for the User on the database.

The error that is thrown is:
Microsoft.SqlServer.Management.Smo.FailedOperationException: Grant failed for User 'RTOUser'. > Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. > System.Data.SqlClient.SqlException: Incorrect syntax near 'CONNECT...'.

With the SQL server profiler, I can see the grant command that was used:
GRANT CONNECT, DELETE, EXECUTE, INSERT, SELECT ON USER::[RTOUser] TO [test14]

RTOUser is just a generic testing account I'm using that basically has admin rights on the database. It also has with grant rights for the aforementioned rights being granted to the test user, test14.

How does one post formatted code on here? a [code] bracket doesn't work nor do I see anything in the formatting bar above for it. Below is the code I'm using:

public override Int32 CreateEmployee( EmployeeData emp )
{
Int32 lastid = 0;
SqlParameter[] insert_parms = {
new SqlParameter("@.EmpUserName", SqlDbType.VarChar, STD_VARCHAR),
new SqlParameter("@.EmpFullName", SqlDbType.VarChar, STD_VARCHAR),
new SqlParameter("@.EmpDescription", SqlDbType.VarChar, STD_VARCHAR)
};

insert_parms[0].Value = emp.m_UserName;
insert_parms[1].Value = emp.m_FullName;
insert_parms[2].Value = emp.m_Description;

try
{
Server svr = SqlHelper.GetServerConnection( this.ConnectionString );
Login lg = new Login( svr, emp.m_UserName );

if ( !svr.Logins.Contains( emp.m_UserName ) )
{
lg.LoginType = LoginType.SqlLogin;
lg.PasswordPolicyEnforced = false; //really should be true
lg.DefaultDatabase = DBNAME;
lg.Create( emp.m_Password );

Database db = svr.Databases[DBNAME];
User u = new User( db, this.UserName );
u.Login = emp.m_UserName;
ObjectPermissionSet perms = new ObjectPermissionSet();

//todo: revise permissions
perms.Connect = true;
perms.Select = true;
perms.Insert = true;
perms.Delete = true;
perms.Execute = true;
u.Grant( perms, emp.m_UserName );
u.Create();

try
{
int.TryParse( SqlHelper.ExecuteScalar( this.DBSqlConnection, CommandType.Text, SQL_INSERT_EMPLOYEE, insert_parms ).ToString(), out lastid );
}
catch(SqlException)
{
throw;
}
}
}
catch (SmoException ex)
{
Console.WriteLine( ex );
}

return lastid;
}Additionally,

the User.Login Intellisense in VS2K5 w/SP1 says "Gets the login that is

associated with the database user". This is wrong since it can also

SET it.|||Bah, figured it out. After thinking what the heck SMO was trying to do with that grant command, I realized how it was doing it wrong. The database needs to grant the rights to the user, not a user to another user.

Functional code snippet:

Database db = svr.Databases[DBNAME];
User u = new User( db, emp.m_UserName );
u.Login = emp.m_UserName;
u.Create();

//todo: revise permissions once user groups functional
DatabasePermissionSet perms = new DatabasePermissionSet();
perms.Select = true;
perms.Insert = true;
perms.Delete = true;
perms.Update = true;
perms.Execute = true;

db.Grant( perms, emp.m_UserName );

No comments:

Post a Comment