Logins, Users, and Roles - Getting Started
-
This article was very helpful for me. I have one question regarding the storage procedure you referred to - sp_addlogin 'SQL_TEST_USER','test'. What does the 'test' part of the script refer to? a db?
-
The first param is the login name, the second is the password for the login.
Andy
-
Excellent, now all I need to work out is how I can use these examples within stored procedures together with error checking so that I can manipulate logins and role members from within an Access 97 frontend. If you can point me towards some examples that would be appreciated.
-
Code should work about the same. Hmm...well, maybe. As I recall it takes a little more work in Access to run a proc, I think a pass through is required. For my money DMO would be the way to go from Access, you'd just set a reference and you could code all you want, no hassle.
Andy
-
quote:
Code should work about the same.I was thinking more about how I would need to expand the code to add error checking. For example the procedure below is called using ADO within Access and adds a user in the Sales table. I intend trying to adapt this to add the user id as a login, add the user to the database, add the user to a role.
This all seems fairly straightforward but I could do with some hints as to error checking - what happens if login exists, user already in database or linked to a role.
I need to do something similar with my update and delete procedures.
CREATE PROCEDURE procUserInsert(
@UserId varchar(7) = NULL,
@Name varchar(25) = NULL,
@Remarks varchar(255) = NULL,
@CountryCode varchar(2) = NULL,
@CountrySuperUser bit = NULL,
@SecurityUser bit = NULL,
@RetCode int = NULL OUTPUT,
@RetMsg varchar(100) = NULL OUTPUT)
AS
SET NOCOUNT ON
DECLARE @Exists int
-- Assume success.
SELECT @RetCode = 1, @RetMsg = ' '
IF @UserId IS NULL
SELECT @RetCode = 0,
@RetMsg = @RetMsg +
'User ID required.' + CHAR(13) + CHAR(10)
-- Check whether record exists
SELECT @Exists = count (*) From SALES WHERE [userID] = @userId
if @Exists >0
SELECT @RetCode = 0,
@RetMsg = @RetMsg +
'User ID already exists.' + CHAR(13) + CHAR(10)
If @RetCode = 0
RETURN
INSERT INTO Sales(
UserId,
[Name],
Remarks,
CountryCode,
CountrySuperUser,
SecurityUser,
ConcurrencyID)
VALUES(@UserId,
@Name,
@Remarks,
@CountryCode,
@CountrySuperUser,
@SecurityUser,
1)
-- Check if insert succeeded. If so, get UserId.
IF @@ROWCOUNT = 1
SELECT @RetCode = 1,
@RetMsg = 'User ID ' + @userid + ' Added'
ELSE
SELECT -- @fldUnique = 0,
@RetCode = 0,
@RetMsg = 'Insertion of new User ID failed..'
RETURN
GOquote:
it takes a little more work in Access to run a proc, I think a pass through is required. For my money DMO would be the way to go from AccessYes it is hard work to run a process from Access but I am adapting examples from 'Microsoft Access Developer's Guide to SQL Server' and these work brilliantly. Wherever possible I am using ADO rather than passthroughs as performance seems better and I am not sure you could handle the error checking as well with a passthrough.
First time I have heard of using DMO Access / SQL Server, wouldn't know where to start!
-
Silly question - it was all much simpler than I thought:
--Add a user
DECLARE @Result int
EXEC @Result = sp_addlogin 'test1', 'test'
EXEC @Result = sp_grantdbaccess 'test1'
EXEC @Result = sp_addrolemember 'kbuser', 'test1'
--Delete a user
DECLARE @Result int
EXEC @Result = sp_revokedbaccess 'test1'
EXEC @Result = sp_droplogin 'test1'
--Change a password
EXEC @result = sp_password NULL, test1, 'test1'
-
I spoke to soon.
Within Access the procedure does not work because the user is not a member of 'the sysadmin and securityadmin fixed server roles'
Is there a workaround. I am using a compiled MDB frontend so security is not an issue but it could be if I had to give users access to one of these fixed server roles.
-
You either have to grant the NT user permission, or set up a sql login that has permission. Not great options. Only other way is to put the requests into a table someplace, have a job that runs with sufficient permissions do the work. Of course that could be a huge security hole as well.
Andy
-
These are actually SQL Server logins, I found that NT logins work to slowly on our WAN.
Regarding your second suggestion, if the user adds a user id to a table, which is in fact what happens how would I then create a login, add user to a database and role? Excuse the dumb questions but I am very green when it comes to SQL Server.
-
You can't truly add a user to a database without adding the login first (note, you can, but then it's orphaned and you'd have to fix it, so just dont). In practice I think its rare to give users a sql login/password, most times it's hidden in the application, so all users connect with the same permissions. If you need to regular access to parts of the app/different functions, you can write your own login code that will allow you to just put the app login/password in a table of your choosing.
I've got a couple more advanced articles about sql logins here on the site, you might want to read those to get a deeper understanding.
Andy
-
Andy, sorry my terminology was confusing. When I referred to users, I really meant the individuals who will use the database.
Currently I store all individuals in a table: Id, name, country and a number of boolean fields. When the Access front end connects, it loads this data for the individual and depending on the combination of id, country, and boolean fields determines what the individual is allowed to do in the database.
The Access frontend connects to the SQL Server backend using SQL Server Authentication using the same id as stored in the table. Currently the SQL Server login and password, access to a database and membership of a role has to be set up separately in SQL Server.
I am trying to work out how I can automate this so that when a new individual is added to my table the following procedures would run automatically:
sp_addlogin, sp_grantdbaccess, sp_addrolemember
And when deleting: sp_revokedbaccess, sp_droplogin
And also provide an option for the administrator to change the users password.
I have tried including these in the procedures which add, delete and modify the individuals table. But get the message "Only members of the sysadmin and securityadmin fixed server roles can execute sp_addlogin.
I then thought I might be able to use a trigger when an individual added to the table but get the message "the procedure 'sp_addlogin' cannot be executed within a transaction'.
Your earlier reply suggested there was a way round this, could you explain further:
quote:
Only other way is to put the requests into a table someplace, have a job that runs with sufficient permissions do the workHope you can help
-
Hi Andy,
I am beginning to use SQLDMO, and am not a professional programmer, so please bear with me.
I need to know how to determine the current user's role membership to set the .Visible method of a control in an Access .adp form. Does that make sense? What I am trying to achieve is this...
- Sub Form_Load()
- 'Load value of current user's highest level role into mstrHighestRole
- Select Case mstrHighestRole
- Case "CustomUserRole"
- Me.txtTextBoxControl.Visible = False
- Case "CustomSupervisorRole"
- Me.txtTextBoxControl.Visible = True
- End Select
- End Sub
I have searched the Internet, SQL Books Online, Microsoft KnowledgeBase and MSDN Online. I can find many articles such as yours about how to create a user and assign the user to a role, but no solution to actually using the role programmatically. Any suggestions would be gratefully appreciated.
- Rob Frittmann
- DBA, Greene Security Ltd
- Auckland, New Zealand
-
I'd just like to echo the comments above, great article. I'm in the process of 'cleaning up' the various logins and users we have and this will help no end.
Growing old is mandatory, growing up is optional -
Is there any way of determining why an NT login has been authenticated to SQL Server? For example given this scenario
1. A user logins in to SQL using NT Login Domain\Jbloggs
2. SQL looks through the list of NT logins & NT Groups to determine if Domain\Jbloggs is allowed in
3. SQL determines Domain\Jbloggs is allowed in because of membership of the local admins group on the server which matches BUILTIN\Administrators in SQL
Can we now execute a SQL statement to confirm that Domain\Jbloggs was given access to the server because of his membership of the BUILTIN\Administrators login?
This would be enourmously useful in troubleshooting permissions issues such as user X can login in but user y can't etc etc.
-
Hi Andy,
This is a wondeful article for anyone who is new to SQL Server. When I started of with SQL Server I had major gaps in my understanding of basic security (logins, users and roles)
. If this article had existed then, may be I would have been a better DBA today 
This is a must read for any newbie to SQL Server.
Great article and keep up the great work Andy
Regards,
Karthik
Viewing 15 posts - 16 through 30 (of 36 total)
You must be logged in to reply to this topic. Login to reply