SQL Server Performance

difference between user and logins

Discussion in 'SQL Server 2005 General Developer Questions' started by shankbond, Sep 17, 2009.

  1. shankbond New Member

    HI,
    I am new to sql. can any one please tell what is a login and what is a user in sql server 2005, I am unable to differentiate between them[:mad:]
  2. rohit2900 Member

  3. shankbond New Member

    [quote user="rohit2900"]Tell me if this helps.[/quote]
    Hi rohit,
    both of the links are great for noobs[Y]
  4. shankbond New Member

    [quote user="rohit2900"]Tell me if this helps.[/quote]
    Hi Rohit,
    now after a brief and good understanding of users and logins I would like to create a login and map a user to it to access a specific database and give him rights to only use select statements to a specific table.
    database= tester
    table=table1
    please guide me
    I searched the google there are a few msdn links but they are too much for a newbie.
  5. FrankKalis Moderator

    USE master;
    GO
    IF SUSER_ID('your login name here ') IS NULL
    BEGIN
    CREATE LOGIN [your login name here] FROM WINDOWS; --change this if you want a SQL authenticated login
    GRANT CONNECT SQL TO [your login name here];
    END

    USE <your database>
    GO

    IF DATABASE_PRINCIPAL_ID('your user name here') IS NULL
    CREATE USER [your user name here] FOR LOGIN [your login name here];
    IF DATABASE_PRINCIPAL_ID('Testers') IS NULL
    CREATE ROLE [Testers] AUTHORIZATION [dbo];
    EXEC sys.sp_addrolemember 'Testers, 'your user name here';
    GRANT SELECT on table1 TO Testers;

  6. shankbond New Member

    First of all thanks again for the help related to links by Rohit and Frank for the help he provided regarding users and logins, roles etc.
    here are the things I understand now after executing the above code given by Frank:
    1: SUSER_ID : is the identification number generated when we create a user.
    Does that mean for every login that we create either sql server authentication or windows they all have a identification number?
    2: DATABASE_PRINCIPAL_ID: it is again an identification number for the corresponding mapping of a username to a login
    or for creating a role for a database.
    Am I correct here?
    3: I have completed the execution of the query provided above:
    after completion I wasn't able to login to sql server through management studio when I used the login and the password I just created ?
    I used this code:
    USE master;
    GO
    IF SUSER_ID('hello1') IS NULL
    BEGIN
    CREATE LOGIN [hello1] WITH PASSWORD= 'hellohello' ; --change this if you want a SQL authenticated login
    GRANT CONNECT SQL TO [hello1];
    END

    USE database1;
    GO

    IF DATABASE_PRINCIPAL_ID('tester') IS NULL
    CREATE USER [tester] FOR LOGIN [hello1];
    IF DATABASE_PRINCIPAL_ID('databse1') IS NULL
    CREATE ROLE [databse1] AUTHORIZATION [dbo];
    EXEC sys.sp_addrolemember 'databse1', 'tester';

    GRANT SELECT on dbo.ghi TO tester;

    Please reply
  7. satya Moderator

    I believe you need to download the latest version of books online to get such basic and best resource information about the topics you have raised. Not that we would not help you but it is best for you always to refer as first hand help, http://sqlserver-qa.net/blogs/tools/archive/2007/04/02/books-online-search-funcationality.aspx if you want to search on web.
    Anyway coming back to your questions reply:
    Yes, SUSER_ID returns an identification number only for logins that have been explicitly provisioned inside SQL Server. This ID is used within SQL Server to track ownership and permissions. This ID is not equivalent to the SID of the login that is returned by SUSER_SID. If login is a SQL Server login, the SID maps to a GUID. If login is a Windows login or Windows group, the SID maps to a Windows security identifier.
    DATABASE_PRINCIPAL_ID can be used in a select list, a WHERE clause, or anywhere an expression is allowed, as in BOL SELECT DATABASE_PRINCIPAL_ID();
    will retrieve ID of current user.
    So what is the error you are getting finally after executing both scripts?
  8. shankbond New Member

    thanks Satya for YOUR reply,
    [quote user="satya"]So what is the error you are getting finally after executing both scripts?[/quote]
    as I have already mentioned that after running those scripts the commands were completed successfully.
    then I tried to test my new user login and the password in management studio, but I was unable to get through?
    I will post the technical details of the error:
    I don't understand what it says though[;)]
    ===================================

    Cannot connect to SERVER.

    ===================================

    Login failed for user 'hello1'. (.Net SqlClient Data Provider)

    ------------------------------
    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476

    ------------------------------
    Server Name: SERVER
    Error Number: 18456
    Severity: 14
    State: 1
    Line Number: 65536


    ------------------------------
    Program Location:

    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
    at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
    at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
    at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
    at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
    at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
    at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
    at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
    at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
    at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
    at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
    at System.Data.SqlClient.SqlConnection.Open()
    at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)
    at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()


  9. shankbond New Member

    Hi guys,
    suddenly I have come across a page which says that I should check whether I have both sql authentication and windows authentication on?
    where as my server says that windows authentication mode.
    is this the problem which created the above error ?
  10. FrankKalis Moderator

    Yes, most likely this is the root cause. You need to configure the server to Mixed Mode Authentication.
    In SQL Server Management Studio open the "Registered Server" window. Right-click on the server you want to configure. Go to the "Security" tab. Switch from "Windows Authentication mode" to "SQL Server and Windows Authentication mode". Click "OK". Right-click again on the server in question and choose "Restart" to acknowledge the change in authentication mode.
  11. rohit2900 Member

    Try what Frank suggested.. As its cleared from the script you've posted that the login which you've created is sql login and your instance is running under windows authentication.........
  12. shankbond New Member

    [quote user="FrankKalis"]Yes, most likely this is the root cause. You need to configure the server to Mixed Mode Authentication.[/quote]
    cool it worked [A] lets have a [^]
    cheers
    [:D]
  13. shankbond New Member

    guys but I don't see any other of tables in other databases, does it mean I will have to map my login with the users in those databases only then will I be able to see or perform activities on them?
  14. rohit2900 Member

    Whenever you give access to a login for a database then a user is created for this login in the specified db... So in order to get access to any other db you need to provide access to this user for each n every db you want it to access....
  15. shankbond New Member

    ok thanks,
    RAM
  16. rohit2900 Member

  17. FrankKalis Moderator

    Simplified speaking is a login on the server level and allows you to connect to a server, while a user is on the database level and gives you access to a database.
    See: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/3f7adbf7-6e40-4396-a8ca-71cbb843b5c2.htm
  18. FrankKalis Moderator

    Oops, maybe I should refresh the browser before posting. [:)]

Share This Page