Usuarios, permisos y SQL Server
01 Dec 2009Recientemente me encontré ante la situación de tener que configurar, en un proyecto de Database de Visual Studio 2008, una serie de usuarios, permisos y roles.
En concreto, necesitaba crear un rol que pudiera ejecutar procedimientos almacenados y hacer SELECTs sobre un determinado esquema. A este rol, a su vez, pertenecería un usuario que también debía crearse dinámicamente en el script. Tras una mañana de investigación, estas son mis conclusiones.
Logins y Users
En SQL Server hay que distinguir entre Logins y Users. Los Logins se crean a nivel de servidor de base de datos. Pueden crearse como Logins de SQL Server, o pueden crearse a partir de una cuenta Windows, lo que en general es más seguro por utilizarse la autenticación integrada. En mi caso era precisamente eso lo que pretendía hacer, crearlos a partir de una determinada cuenta Windows. Esto es muy sencillo de hacer, basta un comando como el que se puede ver a continuación:
USE [yourdb] GO CREATE LOGIN [YourDomainYourUser] FROM WINDOWS GO
Sin embargo, esto tiene una pega: no se puede hacer en los proyectos de base de datos de Visual Studio 2008, o al menos yo no he encontrado el modo. Tiene su sentido, de todas formas, puesto que estos proyectos no dejan de ser la creación de una base de datos concreta, y no debería afectar a la configuración del servidor en sí.
Una vez creado el Login, ya podemos crear un usuario y asociarlo a él. Esta parte sí se puede añadir al proyecto de BD de VS2008, en concreto en la carpeta Users dentro de Security. El TSQL necesario sería algo así:
CREATE USER [YourDomainYourDbUser] FROM LOGIN [YourDomainYourUser] GO
Bien, con esto ya tendríamos nuestro usuario concreto de la base de datos creado, asociado a un Login a nivel del servidor.
Roles y permisos
El siguiente paso es crear el rol al que vamos a asociar el usuario que recién hemos creado. Este rol va a pertenecerle a dbo. No podemos darle sus permisos directamente en el script que se crea en la carpeta Database Roles de Security, esa parte tendremos que añadirla en un script de Post-deployment. Así que en el script de creación del rol simplemente tendríamos la siguiente sentencia TSQL:
CREATE ROLE [YourNewRole] AUTHORIZATION [dbo] GO
Quedarían dos pasos por ejecutar: darle los permisos al rol que necesite y asociarle el usuario que creamos al principio.
La primera parte se completa con una sentencia parecida a ésta. Sería necesario indicar qué esquema de los existentes en la base de datos, va a poder el rol ejecutar sus SPs.
GRANT EXECUTE ON SCHEMA ::[YourSchema] TO [YourNewRole] GO
En cuanto a la segunda, bastaría algo como lo que sigue, apoyándonos en uno de los procedimientos almacenados del sistema:
EXEC sp_addrolemember N'YourNewRole', N'YourDomainYourUser' GO
Bibliografía: